Home » SQL & PL/SQL » SQL & PL/SQL » problem with implementing a scenario in sql due to group by limitation (Oracle 10g)
icon5.gif  problem with implementing a scenario in sql due to group by limitation [message #316860] Mon, 28 April 2008 10:59 Go to next message
ydsouza
Messages: 3
Registered: April 2008
Junior Member
Hi,
I am trying to write an sql code that does the following:
1. select a number of columns from a table; say a,b,c,d,e,f from table X.
2. The table structure is as follows:
a- VARCHAR2
b- VARCHAR2
c- DATE
d- NUMBER
e- NUMBER
f- NUMBER
3. This is the sql I am trying to write:

SELECT a, b, c, MAX(d), e, f FROM X WHERE c>=[parameter input] AND c<[parameter input] GROUP BY a

My problem is that I want to be able to get more columns from the table while group on only one column and SQL tells me "ORA-00979: not a GROUP BY expression". I need to get all the columns showing so do not suggest that I do this:
SELECT a, MAX(d) FROM X WHERE c>=[parameter input] AND c<[parameter input] GROUP BY a
Kindly let me know how I can solve this.
Thank you so much.
Re: problem with implementing a scenario in sql due to group by limitation [message #316863 is a reply to message #316860] Mon, 28 April 2008 11:23 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That is not possible.

If you group by A, and then have multiple rows with the same value for A, but different values for D, for example :

A   D
1   4
1   5
1   6
2   7
2   8
2   9


Then you return two rows with A=1 and A=2 if you group by A

Which value of the multiple values for D should be displayed on those two rows then?

Also, read the forum guidelines on posting and follow them in the future.
Re: problem with implementing a scenario in sql due to group by limitation [message #316865 is a reply to message #316860] Mon, 28 April 2008 11:37 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Try to use the max in its analytical format. What you are trying to do is not aggregation.

Check this link for how to use analytic functions.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#i81407

Regards

Raj
Previous Topic: cursor to table in anonymous block
Next Topic: Convert Number (16 bytes) to encrypted raw value
Goto Forum:
  


Current Time: Fri Dec 02 13:51:39 CST 2016

Total time taken to generate the page: 0.05105 seconds