CODEWITHSUNDEEP
sqlselectgreatest-n-per-group
Troskyvs
Troskyvs

Reputation: 8087

How to write SQL to select rows that has the max(value) of each group?

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

Answers (3)

ajaykumar mp
ajaykumar mp

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

user330315
user330315

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

Gordon Linoff
Gordon Linoff

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

Related Questions