create table employees(id int PRIMARY KEY, name varchar(30),salary int,department_id int);
insert into employees values(1,'ruchi',10000,2);
insert into employees values(2,'ritu',7000,2);
insert into employees values(3,'rima',7000,2);
insert into employees values(4,'koel',5000,1);
insert into employees values(5,'huma',2000,1);
insert into employees values(6,'manav',4500,1);
insert into employees values(7,'sayan',3500,1);
insert into employees values(8,'shrey',9000,2);
insert into employees values(9,'garima',1000,2);
I want to get the top 3 highest paid employees in each department.
>Solution :
You can do it using row_number() function to order your data by salary :
with cte as (
select *, row_number() over(partition by department_id order by salary desc) as rn
from employees
)
select id, name, salary, department_id
from cte
where rn <= 3
order by department_id, rn asc;