Highest Salary in each department

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.

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