How do you get the top-paid employee per department in one query?
Options
- GROUP BY dept and use MAX(name) to pick the top employee
- ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC), keep 1 (Correct option)
- ORDER BY dept, salary DESC and then add LIMIT 1 to the query
- SELECT DISTINCT dept and add the highest salary to the result
Answer
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) and keep rn = 1
Why
A window function calculates a value for each row by looking at a group of related rows (the 'window'), without merging rows like GROUP BY does.
PARTITION BY dept restarts numbering in each department; ORDER BY salary DESC puts the highest first, so row number 1 is the top earner.
Use RANK() instead if ties should all be returned.
SELECT * FROM (
SELECT name, dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employees
) t
WHERE rn = 1;Remember
Top N per group → ROW_NUMBER() OVER (PARTITION BY ...).