Reputation: 8087
The table is like below:
employee, department, salary
Jack, 1, 400
Greg, 2, 350
John, 1, 450
Kate, 2, 420
Jane, 3, 300
Jessy, 2, 400
Kevin, 3, 380
I wish to do: Select the row that contains the highest salary of each department, I expect to return:
John, 1, 450
Jessy, 2, 400
Kevin, 3, 380
Here for department 1, John has the highest salary, so I select this whole row.
How to write this SQL?
Upvotes: 0
Views: 115
Reputation: 546
select e1.* from employee e1 where e1.salary in (select max(e2.salary) from employee e2 where e2.department = e1.department);
Upvotes: 1
Reputation:
I typically solve that using window functions:
select employee, department, salary
from (
select employee, department, salary,
dense_rank() over (partition by department order by salary desc) as rnk
from employee_table
) t
where rnk = 1;
Upvotes: 1
Reputation: 1271241
One method uses a correlated subquery:
select e.*
from employee e
where e.salary = (select max(e2.salary) from employee e2 where e2.department = e.department);
Upvotes: 3