CODEWITHSUNDEEP
sqlsql-serverlogic
Ashok Gupta
Ashok Gupta

Reputation: 2297

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

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 ?

Upvotes: 3

Views: 1026

Answers (2)

Sam
Sam

Reputation: 1366

This is typically something where you use the analytical functions:

SELECT *
FROM ( SELECT tblSal.*, tblEmp.*,
              RANK() OVER (PARTITION BY tblEmp.deptid ORDER BY tblSal.salary DESC) as SalaryRank
       FROM tblEmp
       JOIN tblSal ON tblSal.EmpId = tblEmp.EmpId
      )
WHERE SalaryRank = 1

Upvotes: 2

Adriaan Stander
Adriaan Stander

Reputation: 166626

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.

Upvotes: 5

Related Questions