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 want to make a query that find the highest salary for each department.

Answer 1

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

The accepted response to the preceding question does not apply to the following circumstance. Let's imagine we need to identify the workers who earn the most money in each department for the table below.

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

Take note that both Smith and Tom work in the engineering department and earn the highest wage there is. Because MAX() only provides a single number, the query "SELECT DeptID, MAX(Salary) FROM EmpDetails GROUP BY DeptID" will fail. The following query is valid.

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