Home » SQL & PL/SQL » SQL & PL/SQL » GROUP BY prblm
GROUP BY prblm [message #10659] Sun, 08 February 2004 20:25 Go to next message
resy
Messages: 86
Registered: December 2003
Member
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.

 

 

 

 

Report message to a moderator

Re: GROUP BY prblm [message #10666 is a reply to message #10659] Sun, 08 February 2004 23:47 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
So you on the one hand, you group by a column and on the other hand you don't want to group by this column? That's a bit of a contradiction don't you agree?

Can you give a simple sample of your data and specify what output you'd like?

MHE

Report message to a moderator

Re: GROUP BY prblm [message #10669 is a reply to message #10666] Mon, 09 February 2004 00:44 Go to previous message
resy
Messages: 86
Registered: December 2003
Member
ooops!
sorry..
i got the required query..thanx for yopur response.

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
/

Report message to a moderator

Previous Topic: MSAccess - SQLPlus Syntax Problem
Next Topic: ORA-1203
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 20:00:42 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.