The three highest-paid employees in each department
Table employees(id, department_id, name, salary). For every department, return its three highest-paid employees. If several employees tie at the third salary, returning all of the tied rows is acceptable.
-- top 3 salaries per department_idWrite the query.
Rank inside each department with a window function, then filter. Put ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) in a subquery and keep rows where the number is <= 3. Use RANK() to keep employees tied at the third salary.
- ✗Using a global LIMIT 3 that returns three rows overall, not per department
- ✗Trying to pull ranks 1-3 with repeated MAX aggregates
- ✗Confusing RANK and ROW_NUMBER when the third salary is tied
- →How do RANK, DENSE_RANK and ROW_NUMBER differ on tied salaries?
- →How would you write this without window functions, using a correlated subquery?
Rank the rows inside each department, then keep the top three in an outer query.
WITH ranked AS (
SELECT id, department_id, name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id
ORDER BY salary DESC) AS rn
FROM employees
)
SELECT department_id, name, salary
FROM ranked
WHERE rn <= 3
ORDER BY department_id, salary DESC;PARTITION BY department_id restarts the numbering for each department, and ORDER BY salary DESC puts the highest earner at rank 1. A global ORDER BY salary DESC LIMIT 3 would return only three people overall. If ties at the third salary must all be kept, use RANK() instead of ROW_NUMBER() — RANK() gives tied rows the same number, so rn <= 3 keeps them all.
