Thinking habit: Decide what a tie means
SQL: highest paid employee in each department
The question: List the highest paid employee in each department. If two people tie for the top salary, list both.
employees
| emp_id | name | dept | salary |
|---|---|---|---|
| 1 | Asha | Sales | 70000 |
| 2 | Ravi | Sales | 90000 |
| 3 | Neha | Sales | 90000 |
| 4 | Kabir | Tech | 120000 |
| 5 | Meera | Tech | 110000 |
| 6 | Arjun | HR | 65000 |
This query looks right. Run it, decide if it is, and fix it.
Show the answer and why the first query is wrong
Why the usual answer is wrong
SELECT dept, name, MAX(salary) AS salary FROM employees GROUP BY dept;
GROUP BY dept makes one row per department, so only one name can appear. Ravi and Neha both earn 90,000 in Sales, and one of them is dropped.
The answer
SELECT e.dept, e.name, e.salary FROM employees e WHERE e.salary = ( SELECT MAX(x.salary) FROM employees x WHERE x.dept = e.dept );
Compare each employee with the top salary of their own department. Everyone who matches is kept, ties included.
In PostgreSQL the first query doesn't even run: name isn't grouped. SQLite and MySQL quietly pick one name, which is worse because it looks fine.
With RANK
SELECT dept, name, salary
FROM (
SELECT dept, name, salary,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
FROM employees
)
WHERE rnk = 1;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.