Thinking habit: Decide what a tie means
SQL: top 3 salaries in each department
The question: List the employees whose salary is one of the three highest salary amounts in their department.
employees
| emp_id | name | dept | salary |
|---|---|---|---|
| 1 | Asha | Sales | 90000 |
| 2 | Ravi | Sales | 85000 |
| 3 | Neha | Sales | 85000 |
| 4 | Kabir | Sales | 70000 |
| 5 | Meera | Sales | 60000 |
| 6 | Arjun | Tech | 120000 |
| 7 | Isha | Tech | 110000 |
| 8 | Dev | Tech | 100000 |
| 9 | Zoya | Tech | 95000 |
This query looks right. Run it, decide if it is, and fix it.
SELECT dept, name, salary
FROM (
SELECT dept, name, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employees
)
WHERE rn <= 3;Show the answer and why the first query is wrong
Why the usual answer is wrong
SELECT dept, name, salary
FROM (
SELECT dept, name, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employees
)
WHERE rn <= 3;Ravi and Neha both earn 85,000, so ROW_NUMBER uses up places 2 and 3 on them and Kabir's 70,000, the third highest amount in Sales, is left out.
The answer
SELECT dept, name, salary
FROM (
SELECT dept, name, salary,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
FROM employees
)
WHERE rnk <= 3;PARTITION BY dept restarts the ranking in each department. DENSE_RANK gives Ravi and Neha the same rank, 2, so Kabir is 3.
Ask the interviewer what "top 3" means when people tie. Three people, or three amounts? The answer picks ROW_NUMBER, RANK or DENSE_RANK.
Practise more like this, on real business data
324 interview-style questions on 35 tables with the duplicates, NULLs and missing weeks interviewers test. Free.