A better query to find the list of employees who have a maximum salary in their department?

advertisements

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 ?


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.

Something like

;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.

For that you might want to look at RANK (Transact-SQL) or DENSE_RANK (Transact-SQL)instead of ROW_NUMBER.

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.