A better query to find list of Employess who have max salary in their department?

Viewed 978

I have a table: [tblEmp]

EmpId | EmpName | DeptId

and a table [tblSalary]

EmpId | Salary

and I need to find the list of employees who have max salary in their department.

I could achieve this by:

SELECT *
FROM tblEmp
JOIN tblSal ON tblSal.EmpId = tblEmp.EmpId
WHERE LTRIM(STR(deptid)) + LTRIM(STR(salary)) IN (
    SELECT LTRIM(STR(deptid)) + LTRIM(STR(MAX(salary)))
    FROM tblSal
    JOIN tblEmp ON tblSal.EmpId = tblEmp.EmpId
    GROUP BY DeptId
)

Is there a better way to achieve the list ?

2 Answers
Related