MiddleCodeCommonNot answered yet
Query the highest-paid employee per department
Return the top-earning employee in each department.
-- Employee(id, name, salary, dept_id, manager_id)
-- write your query hereWrite the query.
Rank within each department and keep rank 1: SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM Employee) t WHERE rn = 1. PARTITION BY dept_id restarts the ranking per department — the canonical top-N-per-group pattern.
- ✗Selecting a non-aggregated
namealongsideMAX(salary)in aGROUP BY - ✗Applying a global
LIMITinstead of a per-partition rank - ✗Filtering on the company-wide max rather than per-department
- →How would you return the top 3 earners per department instead of just the top 1?
- →Why does
ROW_NUMBERover a partition outperform a correlated subquery here?
Contents
Task
Return the highest-paid employee in each department of the Employee table.
Solution
SELECT id, name, dept_id, salary
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM Employee
) t
WHERE rn = 1;Key points
PARTITION BY dept_idrestarts the numbering within each department.ROW_NUMBER ... = 1takes the top per department; useRANK/DENSE_RANKto include ties.GROUP BY dept_idwith a non-aggregatednameis invalid — this is the top-N-per-group pattern.
Contents
