How to find 3rd highest salary in each department in sql

In this tutorial, we will see how to find how to find 3rd highest salary in each department in sql. 

There are multiple ways you can do it but if you want to find the department-wise highest paid salary with the name then you have to use the rank technique. Rank is a function in the SQL server that assigns a rank to each row with a partition of a result set.

When to use the rank function

If there is a requirement where you want to find a rank with the result set like highest salary with each department and name.

Create a Department table

create table dpt

(code Char(1),

 name varchar(50))

 go

 insert into dpt(code,name) values('H','Human Resource')

 insert into dpt(code,name) values('S','Support')

 insert into dpt(code,name) values('D','Development')

Create an Employee table

create table emp

(

id int primary key identity,

E_code varChar(50),

name varchar(50),

department char(1),

gross_salary money

 )

go

 insert into emp(E_code,name,department,gross_salary)values('EP001','Sanjay','H',10000)

 insert into emp(E_code,name,department,gross_salary)values('EP002','Dixit','S',20000)

 insert into emp(E_code,name,department,gross_salary)values('EP003','Hement','D',30000)

 insert into emp(E_code,name,department,gross_salary)values('EP004','Raj','H',30000)

 insert into emp(E_code,name,department,gross_salary)values('EP005','DK','S',50000)

 insert into emp(E_code,name,department,gross_salary)values('EP006','VK','D',60000)

 insert into emp(E_code,name,department,gross_salary)values('EP007','Jhon','H',5000)

 insert into emp(E_code,name,department,gross_salary)values('EP008','Alice','S',7000)

 insert into emp(E_code,name,department,gross_salary)values('EP009','Smith','D',8000)

Department wise sum of employee salaries with no. of employee

SELECT count(*), SUM(gross_salary),

(Select name from dpt where code=department)department

FROM emp

GROUP BY department

Department wise the highest paid salaries in each department

with CTE as(

select RANK() over(partition by department Order by department desc,gross_salary desc) as [Rank],

e.E_Code,e.name,d.name as department,e.gross_salary from emp e join dpt d  on d.code=e.department

)

select * from CTE

where [Rank]=3

Note: By Passing Rank in the where condition, you can find nth highest salary.

Related Post

Important SQL complex queries interview questions
[Uses] Store procedure in SQL Server