How to find second highest salary in each department in sql server? Example.

select e1.Name,e2.Department,e2.Salary from employee e1
inner join  EmployeeDetails e2 on e1.id=e2.did

select Name,Department,Salary from (
select e1.Name,e2.Department,e2.Salary, dense_rank() over(PARTITION by e2.Department order by e2.Salary desc) as Rank from employee e1
inner join  EmployeeDetails e2 on e1.id=e2.did
) T
where T.Rank=2;

Share: