Results 1 to 3 of 3

Thread: GROUP BY prblm

  1. #1
    Join Date
    Feb 2004
    Location
    inida
    Posts
    62

    Thumbs up Unanswered: 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
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    You can answer this question youself.

    What do you want to be displayed afters selection of this third column? As you can see, there are 2 descriptions for code 005
    005 Premium Assessment
    and
    008 OFID

    Therefore there are 2 records displayed for code 005. What do you want to see?

    You can write the query denepnding on what you want..
    Oracle can do wonders !

  3. #3
    Join Date
    Feb 2004
    Location
    inida
    Posts
    62
    thanx for your response.
    I got the required query.

    SELECT B.FUNC_CODE, a.FUNC_DESC_INST,
    a.FUNC_SHORT_DESC, a.FUNC_DESC_EBAS, a.FUNC_ID, a.EFFECTIVE_DATE
    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
    /

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •