How do you get the top 2 highest-paid employees in each department?
Number the rows within each department using ROW_NUMBER or DENSE_RANK with PARTITION BY dept ORDER BY salary DESC, then filter on that number in an outer query. A lateral join is an alternative.
Explain it simply
Think of each department as its own cricket team list. You rank players inside each team separately and keep the first two. PARTITION BY dept restarts the numbering for each department; ORDER BY salary DESC decides who is first.
Since a window result cannot be used in WHERE of the same level, you compute it in a subquery and filter outside.
Example
SELECT dept, name, salary
FROM (
SELECT dept, name, salary,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk
FROM employees
) ranked
WHERE rk <= 2
ORDER BY dept, salary DESC;DENSE_RANK includes everyone tied for the top two salary values. Switch to ROW_NUMBER if you want exactly two rows per department.
Alternative with a lateral join, which can be faster with an index on (dept, salary DESC):
SELECT d.dept, t.name, t.salary
FROM (SELECT DISTINCT dept FROM employees) d
CROSS JOIN LATERAL (
SELECT name, salary FROM employees e
WHERE e.dept = d.dept ORDER BY salary DESC LIMIT 2
) t;What to say in the interview
"I partition by department, order by salary descending, number the rows, then filter in an outer query. I choose DENSE_RANK or ROW_NUMBER depending on how ties should be handled, and mention a LATERAL join as an index-friendly alternative."
Common mistakes
- Using LIMIT 2 on the whole query, which returns two rows in total, not per group.