CODEWITHSUNDEEP
sqlsql-server
FuzzyJulz
FuzzyJulz

Reputation: 2874

How to return the X greatest rows for each row in a table

Assume I have two tables(below). What is the best way to write a select to get the 2 employees with the highest salary from each department? Assume that there are potentially a lot of 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

Upvotes: 3

Views: 71

Answers (3)

Hell Boy
Hell Boy

Reputation: 981

This CTE method is 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 get it to work - Fuzzyjulz

Note:i have 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

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

The best for such type of selects is OUTER APPLY. It is designed for this type 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 where there are no employees then just change outer apply to cross apply

Upvotes: 3

ughai
ughai

Reputation: 9890

You can use ROW_NUMBER() like this.

;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

Upvotes: 1

Related Questions