top 3 salaries from each dept, Sql Server

top 3 salaries from each dept

/* 
Question 4: Employee Salary Department
Employee(id,name,salary,dept)

Write a query to get results like below -
EmployeeID EmployeeName Salary Next highest salary(in same department) Department
1 A 1000 2000 IT
2 B 2000 3000 IT
.
.
4 2000 4000 HR
*/

create table Employee(id int,name varchar(255),salary int,dept int);
insert into Employee values(1,'pallavi',200,1);
insert into Employee values(2,'El',300,1);
insert into Employee values(3,'Shinchan',300,2);
insert into Employee values(4,'Monika',100,2);

create table departments(dept_id int,dept_name varchar(200));
insert into departments values(1,'IT');
insert into departments values(2,'HR');
insert into departments values(3,'Design');
select * from departments;
select * from Employee;
select
b.dept_name,
a.name,
a.salary
from
(
select
id,dept,name,salary,dense_rank() over (partition by dept order by salary desc) as rnk
from employee
)a
join
(
    select * from departments
)b
on a.dept = b.dept_id
where a.rnk<=3
order by a.id

/* 
Question 5: Find count of emplyees in each department.
If there are no employees in certain department then populate as 0
*/

--select * from Employee;


run \| edit \| history \| help	0