Highest Salary in each department

Question

I have a table EmpDetails:

DeptID      EmpName   Salary Engg        Sam       1000 Engg        Smith     2000 HR          Denis     1500 HR          Danny     3000 IT          David     2000 IT          John      3000 

I need to make a query that find the highest salary for each department.

Answer 1

SELECT DeptID, MAX(Salary) FROM EmpDetails GROUP BY DeptID

The above query is the accepted answer but it will not work for the following scenario. Let's say we have to find the employees with the highest salary in each department for the below table.

DeptID	EmpName	Salary
Engg	Sam	1000
Engg	Smith	2000
Engg	Tom	2000
HR	Denis	1500
HR	Danny	3000
IT	David	2000
IT	John	3000

Notice that Smith and Tom belong to the Engg department and both have the same salary, which is the highest in the Engg department. Hence the query "SELECT DeptID, MAX(Salary) FROM EmpDetails GROUP BY DeptID" will not work since MAX() returns a single value. The below query will work.

SELECT DeptID, EmpName, Salary FROM EmpDetails WHERE (DeptID,Salary) IN (SELECT DeptID, MAX(Salary) FROM EmpDetails GROUP BY DeptID)

Output will be

DeptID	EmpName	Salary
Engg	Smith	2000
Engg	Tom	2000
HR	Danny	3000
IT	John	3000

Answer 2

Assuming SQL Server 2005+

WITH cteRowNum AS (     SELECT DeptID, EmpName, Salary,            DENSE_RANK() OVER(PARTITION BY DeptID ORDER BY Salary DESC) AS RowNum         FROM EmpDetails ) SELECT DeptID, EmpName, Salary     FROM cteRowNum     WHERE RowNum = 1;