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 ?