I have a table:
EmpId | EmpName | DeptId
and a table
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 ?
You could try using ROW_NUMBER.
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
;WITH Employees AS ( SELECT e.*, ROW_NUMBER() OVER(PARTITION BY e.DeptId ORDER BY s.salary DESC) RowID FROM [tblEmp] e INNER JOIN [tblSalary] s ON e.EmpId = s.EmpId ) SELECT * FROM Employees WHERE RowID = 1
This will however not return Employees that have the same salry in the same department.
Rank : Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.
If two or more rows tie for a rank, each tied rows receives the same rank.
Dense_Rank : Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.