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.

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

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?

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

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
/