Find department top 3 salaries

Write a SQL query to find a top 3 highest paid employees in each of the department.

As you , here we have two tables named Employee and Department.

Create table Employee (id int, name varchar(255), salary int, departmentId int);
Create table Department (id int, name varchar(255));

insert into Employee values ('1', 'Manish Kumar', '70000', '1');
insert into Employee values ('2', 'Ajay Singh', '75000', '2');
insert into Employee values ('3', 'Mohit', '60000', '2');
insert into Employee values ('4', 'Robert', '70000', '1');
insert into Employee values ('5', 'Ramesh', '90000', '1');
insert into Employee values ('6', 'Atul Jr.', '95000', '1');
insert into Employee values ('7', 'Sanjay Singh', '60000', '1');
insert into Employee values ('8', 'Mukesh', '40000', '1');

insert into Department (id, name) values ('1', 'CS');
insert into Department (id, name) values ('2', 'HR');

SELECT * FROM Employee;

SELECT * FROM Department;

Solution:

Following T-SQL query returns the top 3 highest paid employees in each of the department.

With CTE AS
(
SELECT E.Id, E.Name AS Employee, E.Salary, D.Name AS Department,
DENSE_RANK() OVER (Partition By D.Name Order By E.Salary DESC) AS Rnk
FROM Employee E
JOIN Department D
ON D.Id = E.DepartmentId
)
SELECT Department, Employee, Salary
FROM CTE WHERE Rnk <=3;

Let’s see the output of above query and you can see, It returns the top 3 highest paid employees in each of the department.

Also Read..

SQL Interview Questions