Re: Need help with SQL query

[Alan Shore <AlanShore@xxxxxxxx>]

John - What do you want to see if the data includes these 2 records

> Fname  lname   age       salary
>
> ----------  ---------   ----        --------
>
> John     Smith    12         220000
> John     Smith    90         12



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
midrange-l-bounces@xxxxxxxxxxxx wrote on 12/06/2006 04:00:17 PM:

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