How to return the X largest rows for each row in a table
Let's say I have two tables (below). What is the best way to write a selection to get the 2 highest salary employees from each department? Suppose there are potentially many departments.
output:
employee_name | salary | department_id
John | 65000 | 1
Sally | 60000 | 1
Lucy | 40000 | 2
James | 80000 | 3
Harry | 65000 | 3
Tables:
Employee
employee_name | salary | department_id
John | 65000 | 1
Sally | 60000 | 1
Connor | 55000 | 1
Judy | 55000 | 1
Lucy | 40000 | 2
James | 80000 | 3
Harry | 65000 | 3
Penny | 56000 | 3
Department
department_id | name
1 | Sales
2 | Marketing
3 | IT
source to share
The best choice for this type of choice is OUTER APPLY
. It is designed for this kind of work:
select d.department_id, oa.employee_name, oa.salary
from Departments d
outer apply(select top 2 e.employee_name, e.salary
from Employee e
where d.department_id = e.department_id
order by e.salary desc) oa
If you don't want to get departments that have no employees, just change OUTER APPLY
tocross apply
source to share
You can use ROW_NUMBER()
as follows.
;WITH CTE as
(
SELECT employee_name,Salary,department_id,
ROW_NUMBER()OVER(PARTITION BY department_id ORDER BY Salary DESC) rn
FROM Employee
)
SELECT employee_name,Salary,d.department_id,d.name
FROM CTE c
INNER JOIN Departments d ON d.department_id = c.department_id
WHERE rn <= 2
source to share
This method is CTE
quite efficient
Use this code:
Create TABLE #table
(
name varchar(10),
salary varchar(10),
depid int
)
insert into #table values('John','65000',1)
insert into #table values('Sally','60000',1)
insert into #table values('Connor','55000',1)
insert into #table values('Judy','55000',1)
insert into #table values('Lucy','65000',2)
insert into #table values('Kevin','55000',2)
insert into #table values('Ram','60000',2)
insert into #table values('James','80000',3)
insert into #table values('Harry','65000',3)
insert into #table values('Penny','56000',3)
select * from #table
;With CTE as
(
select name,salary,depid,ROW_NUMBER() over(partition by depid order by salary desc) as maxisal from #table
)
select name,salary,depid from CTE
where maxisal<=2
EDIT: changed maxi to maxisal to make it work - Fuzzyjulz
Note. I added two entries for Depid 2
Output:
name salary depid
John 65000 1
Sally 60000 1
Lucy 65000 2
Ram 60000 2
James 80000 3
Harry 65000 3
source to share