Home » SQL & PL/SQL » SQL & PL/SQL » Selecting the maximum value from the Group (merged 3) (SQL)
Selecting the maximum value from the Group (merged 3) [message #432040] Fri, 20 November 2009 11:27 Go to next message
shabir46
Messages: 40
Registered: November 2009
Member
Dear All,

Please can you help me out with the query with my requirement?

I have a table as shown below

TEST

A B C
2 40 60
2 50 100
2 50 200
3 40 100

I need to get the result like this

A B
2 50 (This row is picked by taking the row with the maximum
3 40 value of c )


Thanks,
Shabir





Re: Selecting the maximum value from the Group [message #432044 is a reply to message #432040] Fri, 20 November 2009 11:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Either you use a subquery that gives the max for each group, either you use RANK function and take the rows with rank 1.

Regards
Michel
Re: Selecting the maximum value from the Group [message #432045 is a reply to message #432044] Fri, 20 November 2009 11:49 Go to previous messageGo to next message
shabir46
Messages: 40
Registered: November 2009
Member
Thanks Michael.. It worked fine.


SELECT A,B
FROM TEST
WHERE C IN (SELECT MAX(C) FROM TEST GROUP BY A)

Thanks again!!!
Shabir
Re: Selecting the maximum value from the Group [message #432046 is a reply to message #432045] Fri, 20 November 2009 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You must correlate the subquery with the outer one (on the group: a) otherwise you may have wrong results.
In your example, change 2 50 100 to 2 60 100 and you will see you also get 2 60.

Regards
Michel
Re: Selecting the maximum value from the Group [message #432047 is a reply to message #432046] Fri, 20 November 2009 12:14 Go to previous messageGo to next message
shabir46
Messages: 40
Registered: November 2009
Member
Hi Michael,

Thanks for your quick response.


SELECT T1.A,T1.B FROm TEST T1
WHERE T1.C = (SELECT MAX(T2.C) FROM TEST T2 WHERE T1.A = T2.A)

I think this will work fine.


Thank you very much for your help,

Shabir
Re: Selecting the maximum value from the Group [message #432050 is a reply to message #432047] Fri, 20 November 2009 12:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another way to write it is:

SELECT T1.A,T1.B FROm TEST T1
WHERE (T1.A,T1.C) IN (SELECT T2.A, MAX(T2.C) FROM TEST T2 GROUP BY T2.A)

The one that is faster depends on many things like indexes, number of rows, of groups (distinct a)...

Regards
Michel

Re: Selecting the maximum value from the Group (merged 3) [message #432051 is a reply to message #432040] Fri, 20 November 2009 12:35 Go to previous message
shabir46
Messages: 40
Registered: November 2009
Member
Thanks for your great help!!!!!!!

really appreciate it..

Shabir
Previous Topic: How to get a Key based on exact matching value combination
Next Topic: Trigger Help
Goto Forum:
  


Current Time: Fri Dec 09 09:34:24 CST 2016

Total time taken to generate the page: 0.27099 seconds