RE: Need help with SQL query

["John Candidi" <jacandidi@xxxxxxxxxxxxxxxxxxxx>]

There is no primary key

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Wednesday, December 06, 2006 4:29 PM
To: Midrange Systems Technical Discussion
Subject: RE: Need help with SQL query

Okay - In other words, largest salary is the primary key.



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx

midrange-l-bounces@xxxxxxxxxxxx wrote on 12/06/2006 04:13:59 PM:

> Record with the largest salary.  If there are multiple, then the record
with
> the largest salary and the largest age (one record).
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
> Sent: Wednesday, December 06, 2006 4:07 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: Need help with SQL query
>
> 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.
> --
> 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.