Wow is my solution needlessly complicated.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Doug McLauchlan
Sent: Wednesday, December 06, 2006 4:46 PM
To: Midrange Systems Technical Discussion
Subject: RE: Need help with SQL query
SELECT fname, lname, max(age), max(salary) FROM file GROUP BY fname,
lname
Doug McLauchlan
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of John Candidi
Sent: Wednesday, December 06, 2006 1:39 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Need help with SQL query
John Smith 40 60000
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Chris Payne
Sent: Wednesday, December 06, 2006 4:35 PM
To: Midrange Systems Technical Discussion
Subject: RE: Need help with SQL query
Lets say you have
John Smith 25 60000
John Smith 40 20000
John Smith 20 15000
As the only contents of your file, which record do you want back? If we
get the John Smith with the highest salary, or the John Smith with the
highest age we will be down to one John Smith and it will be meaningless
to narrow it down further.
Chris
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of John Candidi
Sent: Wednesday, December 06, 2006 4:00 PM
To: 'Midrange Systems Technical Discussion'
Subject: Need help with SQL query
I am trying to figure out a query that needs to display rows of grouped
data based on max values of two columns.
Here is an example.
Fname lname age salary
---------- --------- ---- --------
John Smith 39 20000
John Smith 38 20000
John Smith 40 15000
Adam Apple 20 25000
Adam Apple 21 25000
Adam Apple 19 18000
I need to return one line from each group (one john smith and one Adam
Apple) based on highest salary and age. For john Smith, highest Salary
is 20000 and then highest age is 39, so the row returned would be John
Smith 39 20000. For Adam Apple it would be Adam Apple 21 25000.
I can get it narrowed down to rows grouped with highest salary, but then
I can not think how to get it to narrow it down another level and only
display the one with the larger age number after that.
John A Candidi
Rutgers Insurance Companies
IT Director - Iseries Manager
856-779-2274
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.
