RE: Need help with SQL query -- MIDRANGE-L

This one only returns the first row in DB

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Hayes, Joe
Sent: Friday, December 08, 2006 10:39 AM
To: Midrange Systems Technical Discussion
Subject: RE: Need help with SQL query

 SELECT fname, lname, age, max(salary) FROM file GROUP BY fname, lname 
        order by age descending
        fetch first 1 row only


-----Original Message-----
From: midrange-l-bounces+joe.hayes=fiserv.com@xxxxxxxxxxxx
[mailto:midrange-l-bounces+joe.hayes=fiserv.com@xxxxxxxxxxxx] On Behalf
Of John Candidi
Sent: Friday, December 08, 2006 9:32 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Need help with SQL query

Actually, I completely made a mistake portraying what I wanted.  I DON'T
want the records combined.  In that example, the result should have
been:
John     Smith    25         60000

Basically, return the row with the highest salary ... if there are
multiple rows with the same highest salary, then only return the row
with the highest salary AND the highest age ... grouped by first name
and last name.

-----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.


--
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.

--
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.


______________________________________________________________________
This e-mail has been scanned by MCI Managed Email Content Service, using
Skeptic(tm) technology powered by MessageLabs. 
______________________________________________________________________

______________________________________________________________________
This e-mail, and any attachments, is intended only for use by the
addressee(s) named herein and may contain legally privileged and/or
confidential information belonging to Planar Systems, Inc.  If you are
not
the intended recipient of this e-mail, you are hereby notified that any
dissemination, distribution or copying of this e-mail, any attachments
thereto, and use of the information contained, is strictly prohibited.
If
you have received this e-mail in error, please notify the sender and
permanently delete the original and any copy thereof.
______________________________________________________________________