Top earner per department

Tables employees(id, name, salary, dept_id) and departments(id, name). Return each department's highest-paid employee(s).

"Find the top X in each group" is one of the most useful SQL patterns you can learn. You'll write it at work far more often than you'd expect: best-selling product per city, latest order per customer, highest-scoring student per class.

The tricky bit: ties. If two people in Sales both earn the top salary, the question says to return both. That rules out a few shortcuts that quietly pick just one.

Example data:

name	salary	dept
Asha	90000	Sales
Ravi	90000	Sales
Meena	70000	IT
Kiran	65000	IT

Expected: Asha and Ravi for Sales, Meena for IT.

💡 Hint 1

Window functions: RANK() OVER (PARTITION BY ...)

✅ Show solution

SELECT d.name AS department, e.name, e.salary
FROM (
  SELECT *, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) rnk
  FROM employees
) e
JOIN departments d ON d.id = e.dept_id
WHERE e.rnk = 1;

How it works

RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) restarts the numbering for each department and gives rank 1 to the highest salary. If two people tie, they both get rank 1. The outer query keeps rank 1 and joins the department name in.

Why not just GROUP BY?

This is the first thing most people try:

SELECT dept_id, name, MAX(salary) FROM employees GROUP BY dept_id;

It looks right, but name here isn't tied to the max salary. MySQL (with strict mode off) will happily return some name from the group, often the wrong one. Newer MySQL versions reject the query outright.

Without window functions

On old MySQL 5.7 servers (still common on shared hosting), use a join on the max:

SELECT d.name AS department, e.name, e.salary
FROM employees e
JOIN (SELECT dept_id, MAX(salary) AS top FROM employees GROUP BY dept_id) m
  ON m.dept_id = e.dept_id AND m.top = e.salary
JOIN departments d ON d.id = e.dept_id;

RANK vs ROW_NUMBER vs DENSE_RANK

ROW_NUMBER never ties: one winner per group, chosen arbitrarily on ties.
RANK ties and then skips (1, 1, 3).
DENSE_RANK ties without skipping (1, 1, 2).

Pick based on what the question says about ties. Here it says "employee(s)", so ties stay.

Tip: run your code in a local Python environment, Jupyter or Google Colab.