✏️ Explanatory Question
Level: Coding Round — A LeetCode "Department Highest Salary" problem; exposes the classic GROUP BY name-mismatch trap.
The Puzzle: For each department, return the employee(s) with the highest salary, showing the department name, employee name, and salary. If multiple employees tie for the top salary in a department, return all of them.
| id | name | salary | dept_id |
|---|---|---|---|
| 1 | Rumman | 90000 | 10 |
| 2 | Krushna | 90000 | 10 |
| 3 | Swetha | 60000 | 10 |
| 4 | Ritesh | 80000 | 20 |
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Sales |
| dept_name | name | salary |
|---|---|---|
| Engineering | Rumman | 90000 |
| Engineering | Krushna | 90000 |
| Sales | Ritesh | 80000 |
Both Rumman and Krushna tie at 90k in Engineering, so both appear.
The classic trap: Writing SELECT dept_id, name, MAX(salary) ... GROUP BY dept_id is wrong. The name is not tied to the MAX(salary) row — MySQL returns an arbitrary name (and MySQL 8.0 errors under ONLY_FULL_GROUP_BY). You cannot get the "row of the max" from a simple GROUP BY.
SELECT d.dept_name, e.name, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary = (
SELECT MAX(e2.salary)
FROM employees e2
WHERE e2.dept_id = e.dept_id -- the top salary for THIS department
);
-- Because it matches ALL rows equal to the max, ties are includedSELECT dept_name, name, salary
FROM (
SELECT d.dept_name, e.name, e.salary,
RANK() OVER (
PARTITION BY e.dept_id ORDER BY e.salary DESC
) AS rnk
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
) t
WHERE rnk = 1; -- RANK gives all tied top earners the same rank = 1 Why RANK (not ROW_NUMBER): To include all tied top earners, use RANK() or DENSE_RANK() — tied salaries all get rank 1. ROW_NUMBER() would arbitrarily pick just one of the two 90k earners, dropping the other. Choose the ranking function based on whether ties should be kept.
SELECT d.dept_name, e.name, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
JOIN (
SELECT dept_id, MAX(salary) AS max_sal
FROM employees GROUP BY dept_id
) m ON e.dept_id = m.dept_id AND e.salary = m.max_sal;
-- Join back to the per-dept max; matching rows are the top earners (ties included) Interviewer follow-up: "What if you want exactly ONE top earner per department, even on ties?" → Switch to ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, id) and filter rn = 1. The id tie-breaker deterministically picks a single winner. It is the opposite requirement from this puzzle — and the reason knowing RANK vs ROW_NUMBER matters so much.