How to get top salary of each department

To find the highest salary of employee in each department, first we will create a sample tables named EmployeeDetails and Department as shown below.

CREATE TABLE dbo.Department(DeptId int, DepartmentName varchar(100))

INSERT INTO dbo.Department(DeptId, DepartmentName)
values (101, 'Logistics'),
(102, 'Computer Science'),
(103, 'Human Resource'),
(104, 'Finance')

CREATE TABLE dbo.EmployeeDetails
(EmpID int, DeptId int, EmpName varchar(100), Salary int)

INSERT INTO dbo.EmployeeDetails(EmpID, DeptId, EmpName, Salary)
VALUES 
(501, 101, 'Rohit Kumar', 5000),
(502, 101, 'Sujit Singh', 10000),
(503, 101, 'Sumit Kumar', 8000),
(504, 102, 'Ajay Rawat', 7000),
(505, 102, 'Sonu Agarwal', 5000),
(506, 103, 'Rohit Singh', 6500),
(507, 104, 'Manjit Kumar', 5000)

Now write the following T-SQL query, which assigns a row number or you can say rank or sequence number in descending order based on salaries of each employee’s within a department.
Means for any department employee having highest salary will have rank 1 and employee having lowest salary will have highest rank.

SELECT DepartmentName, EmpName, Salary, 
ROW_NUMBER() OVER(PARTITION BY ed.DeptId ORDER BY Salary Desc) AS seq
FROM 
Department dpt INNER JOIN EmployeeDetails ED
ON dpt.DeptId = ED.DeptId

Lets see the output of above T-SQL and you can see, each employee has rank within their department based on their salary.

You can see seq column, rank 1 are assigned to employees who have highest salary in their department.

Now we need employee having rank 1 only that means who are having highest salary in each department.

Lets modify above T-SQL as following.

We will select only those records who rank 1 that means seq =1, so that we can get employees who have highest salary only.

SELECT * FROM (
SELECT DepartmentName, EmpName, Salary, 
ROW_NUMBER() OVER(PARTITION BY ed.DeptId ORDER BY Salary Desc) AS seq
FROM 
Department dpt INNER JOIN EmployeeDetails ED
ON dpt.DeptId = ED.DeptId
)tbl 
WHERE Seq = 1

Now you can see, it returns only those employees who have highest salary.

If you do not want to see seq column in final output then you can specified columns that you want to see in your final output as shown below.

SELECT DepartmentName, EmpName, Salary FROM (
SELECT DepartmentName, EmpName, Salary, 
ROW_NUMBER() OVER(PARTITION BY ed.DeptId ORDER BY Salary Desc) AS seq
FROM 
Department dpt INNER JOIN EmployeeDetails ED
ON dpt.DeptId = ED.DeptId
)tbl 
WHERE Seq = 1

Similarly, if you want to see top 2 highest salary in each department then you can modify above T-SQL as shown below.

SELECT DepartmentName, EmpName, Salary FROM (
SELECT DepartmentName, EmpName, Salary, 
ROW_NUMBER() OVER(PARTITION BY ed.DeptId ORDER BY Salary Desc) AS seq
FROM 
Department dpt INNER JOIN EmployeeDetails ED
ON dpt.DeptId = ED.DeptId
)tbl 
WHERE Seq <=2

You can see above output, now it returns top two employees having highest salary in their department, similarly you can modify T- SQL to see the Nth highest salary in each department.

Also Read

SQL Server Interview Question & Answers