× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.