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

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;