[Solved] How to select highest paid employee in dept based on the following? [closed]

by


If you are using SQL-Server, use ROW_NUMBER or DENSE_RANK(if you want to include ties):

WITH CTE AS(
    SELECT e.EmpID,e.EmpName,e.EmpSalary, e.Department,b.EmpBonus,
       RN = ROW_NUMBER() OVER (PARTITION BY Department 
                               ORDER BY (EmpSalary + COALESCE(EmpBonus,0)) DESC)
    FROM Employees e LEFT OUTER JOIN Bonuses b
       ON e.EmpID = b.EmpID 
)
SELECT EmpID, EmpName, EmpSalary, Department, EmpBonus
FROM CTE
WHERE RN = 1

Ranking Functions

4

solved How to select highest paid employee in dept based on the following? [closed]