Re: Need help with SQL query

[Billy Rowe <billyrowe@xxxxxxx>]

   -- This should give you 1 of the rows that have the max AGE and SALARY.
   -- Since you have a possibilty of having duplicates, the 'distinct'
   keyword
   -- can be used to return one row or not if all rows ( even duplicates )
   are wanted.
   Select distinct emp.fname, emp.lname, emp.age, emp.salary
    from  emp,
          ( select fname, lname, max( age ), max( salary )
             from  emp
            group by fname, lname ) emx
   where  emp.fname   =  emx.fname
    and   emp.lname   =  emx.lname
    and   emp.age     =  emx.age
    and   emp.salary  =  emx.salary

   hth

 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-----
 SELECT fname, lname, max(age), max(salary) FROM file GROUP BY fname,lname

 -----Original Message-----
 John     Smith    40    60000

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

 -----Original Message-----
 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