Group values by another fields max value

Question

I have the following Table

ID | Name | Salary
---+------+-------
1  |Tom   | 100
2  |Tom   | 600
3  |Max   | 300
4  |Jim   | 400
5  |Max   | 200
6  |Tom   | 600
7  |Jim   | 100

I try to get those IDs and names which have the max salary.

Expected Result

ID | Name 
---|------
2  | Tom
3  | Max
4  | Jim

Actual Result

ID | Name 
---|------
2  | Tom
6  | Tom << duplicate name
3  | Max
4  | Jim

The Filter is done by

SELECT e1.ID, e1.Name
FROM Employee e1
WHERE e1.Salary = 
(
  SELECT MAX(e2.Salary) 
  FROM Employee e2
  WHERE e1.Name = e2.Name
);

Unfortunately duplicates appear twice if the max salary appears twice - how do I have to change the subselect to suppress duplicate names

Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Thom A, Feb 09 '18 at 12:55
Have a look at the answer in the link. It's relevant; and there are 1,000's of answer out there on how to only return the first value of a set of duplicate results. — Thom A, Feb 09 '18 at 12:56

score 2 · Accepted Answer · answered Feb 09 '18 at 12:55

2

This would normally be done using row_number():

select . ..
from (select e.*, row_number() over (partition by name order by salary desc) as seqnum
      from employee e
     ) e
where seqnum = 1;

answered Feb 09 '18 at 12:55

Gordon Linoff

1 Answers1