Thread: GROUP BY prblm

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    11
    Rep Power
    0

    Thumbs up GROUP BY prblm


    hi,

    please read the following query

    SQL> ed
    Wrote file afiedt.buf

    1 SELECT FUNC_CODE, MAX(EFFECTIVE_DATE) as MX_DATE FROM TCBN_FUNCTION_MST WHERE
    2 UPPER(FUNC_CODE) LIKE('%') AND FUNC_CODE IN (SELECT DISTINCT
    3 FUNC_CODE FROM TCBN_OPER_FUNC_MST WHERE OPER_CODE LIKE ('001')) AND
    4 UPPER(FUNC_DESC_INST) LIKE('%') AND UPPER(FUNC_ID) LIKE('%') AND
    5 UPPER(DELETED) NOT LIKE 'Y' OR UPPER(DELETED) IS NULL
    6* GROUP BY FUNC_CODE
    SQL> /

    FUN MX_DATE
    --- ---------
    001 20-JAN-04
    003 05-JAN-04
    005 03-JAN-04
    008 20-JAN-04


    In the above query i want 2 add more fields, but i want the same output 2 be displayed. here is the query i tried..but itz grouping by all the fields...



    SQL> ed
    Wrote file afiedt.buf

    1 SELECT FUNC_CODE, FUNC_DESC_INST, MAX(EFFECTIVE_DATE) as MX_DATE
    2 FROM TCBN_FUNCTION_MST
    3 WHERE
    4 UPPER(FUNC_CODE) LIKE('%') AND FUNC_CODE IN (SELECT DISTINCT
    5 FUNC_CODE FROM TCBN_OPER_FUNC_MST WHERE OPER_CODE LIKE ('001')) AND
    6 UPPER(FUNC_DESC_INST) LIKE('%') AND UPPER(FUNC_ID) LIKE('%') AND
    7 UPPER(DELETED) NOT LIKE 'Y' OR UPPER(DELETED) IS NULL
    8* GROUP BY FUNC_CODE, FUNC_DESC_INST
    SQL> /

    FUN FUNC_DESC_INST MX_DATE
    --- ---------------------------------------- ---------
    001 BANK RETURNS 20-JAN-04
    003 DISCOUNT HOUSE RETURNS 05-JAN-04
    005 TED 02-JAN-04
    005 Premium Assessment 03-JAN-04
    008 OFID 04-JAN-04
    008 PRE EXAMINATION REQUIREMENTS 20-JAN-04

    6 rows selected.


    any kind of solutions are highly appreciated.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    99
    Rep Power
    12
    Problem is not with the Query but whith how the data is stored. Seems like there are 2 Description for the Same Function Code (005, 008) No matter what you do the output will be same for the Second Query.
    hence Make sure that there is one and only one description for each function code. Then you should get the Same Output as your first query
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    11
    Rep Power
    0
    Actually the function code's will be repeating and it will have different description also.
    But i want to display only those function code with its corresponding description where the max(effective) date comes.

    so any more suggestions?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    99
    Rep Power
    12
    Try This Query this should give you the result as you want:

    SELECT B.FUNC_CODE, a.FUNC_DESC_INST, b.EFFECTIVE_DATE as MX_DATE
    FROM TCBN_FUNCTION_MST A, (Select Func_Code, MAX (EFFECTIVE_DATE) AS EFFECTIVE_DATE FROM FROM TCBN_FUNCTION_MST GROUP BY FUNC_CODE) B

    WHERE A.FUNC_CODE = B.FUNC_CODE
    AND A.Effective_Date = b.Effective_Date
    AND UPPER(A.FUNC_CODE) LIKE('%')

    AND A.FUNC_CODE IN (SELECT DISTINCT
    FUNC_CODE FROM TCBN_OPER_FUNC_MST WHERE OPER_CODE LIKE ('001')) AND
    UPPER(FUNC_DESC_INST) LIKE('%') AND UPPER(FUNC_ID) LIKE('%') AND
    UPPER(DELETED) NOT LIKE 'Y' OR UPPER(DELETED) IS NULL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    11
    Rep Power
    0
    THANX FOR UR REPLY.
    BUT THAT QUERY GIVES ME MULTIPLE RECORDS.

    Here is the worked query

    SELECT B.FUNC_CODE, a.FUNC_DESC_INST
    FROM TCBN_FUNCTION_MST A,
    (Select Func_Code, MAX (EFFECTIVE_DATE) AS EFFECTIVE_DATE
    FROM TCBN_FUNCTION_MST
    WHERE UPPER(FUNC_CODE) LIKE('%')
    AND FUNC_CODE IN (SELECT DISTINCT
    FUNC_CODE FROM TCBN_OPER_FUNC_MST WHERE OPER_CODE LIKE ('001')) AND
    UPPER(FUNC_DESC_INST) LIKE('%') AND UPPER(FUNC_ID) LIKE('%') AND
    UPPER(DELETED) NOT LIKE 'Y' OR UPPER(DELETED) IS NULL
    GROUP BY Func_Code)B
    WHERE A.FUNC_CODE = B.FUNC_CODE
    AND A.Effective_Date = b.Effective_Date
    /

IMN logo majestic logo threadwatch logo seochat tools logo