Top Salaries per Department in SQL
Learn partitioned ranking by finding the top three distinct salaries inside each department, including ties and window-function trade-offs.
The problem
Given Employee and Department tables, return every employee whose salary is among the top three distinct salary values in that employee’s department. If multiple employees tie at one of those salary values, include all of them.
Top-N-per-group appears in dashboards, compensation analysis, sales reporting, monitoring, and interview questions. The difficult part is not sorting—it is realizing that ranking must restart for every department and that ties change the meaning of “top three.”
Schema and sample data
Department
| idINTEGER | nameVARCHAR |
|---|---|
| 10 | Engineering |
| 20 | Sales |
| 30 | Support |
id — Unique department identifier.
name — Department name.
Schema and sample data
Employee
| idINTEGER | nameVARCHAR | salaryINTEGER | department_idINTEGER |
|---|---|---|---|
| 1 | Asha | 140000 | 10 |
| 2 | Ben | 125000 | 10 |
| 3 | Chen | 125000 | 10 |
| 4 | Diego | 110000 | 10 |
| 5 | Eleni | 100000 | 10 |
| 6 | Fatima | 115000 | 20 |
| 7 | Gabe | 98000 | 20 |
| 8 | Hana | 92000 | 20 |
| 9 | Ivan | 87000 | 20 |
| 10 | Jules | 76000 | 30 |
id — Unique employee identifier.
name — Employee display name.
salary — Annual salary.
department_id — Foreign key to Department.id.
Expected output
| department | employee | salary |
|---|---|---|
| Engineering | Asha | 140000 |
| Engineering | Ben | 125000 |
| Engineering | Chen | 125000 |
| Engineering | Diego | 110000 |
| Sales | Fatima | 115000 |
| Sales | Gabe | 98000 |
| Sales | Hana | 92000 |
| Support | Jules | 76000 |
Predict the result first
Before writing SQL, rank the Engineering salaries as distinct values: 140000 is rank 1, 125000 is rank 2, 110000 is rank 3, and 100000 is rank 4. Ben and Chen must both survive because they share rank 2. Now ask the key question: what SQL feature can restart that ranking when the department changes?
Show a hint
Use a window function that assigns equal salaries the same rank. Partition the window by department_id so each department gets its own independent ranking, then filter to ranks 1 through 3 outside the windowed query.
DENSE_RANK within each department
WITH ranked AS (
SELECT
e.id,
e.name,
e.salary,
e.department_id,
DENSE_RANK() OVER (
PARTITION BY e.department_id
ORDER BY e.salary DESC
) AS salary_rank
FROM Employee e
)
SELECT
d.name AS department,
r.name AS employee,
r.salary
FROM ranked r
JOIN Department d ON d.id = r.department_id
WHERE r.salary_rank <= 3
ORDER BY d.name, r.salary DESC, r.name;PARTITION BY department_id splits the rows into independent ranking groups. The window function still sees every employee, but the rank counter restarts for each department.
DENSE_RANK gives equal salaries the same rank and does not leave gaps after ties. In Engineering, both 125000 rows receive rank 2 and 110000 still receives rank 3.
The outer query filters to ranks 1 through 3, then joins Department only to obtain the display name. Separating ranking from presentation keeps the ranking rule easier to inspect.
Trade-offs
- The intent maps directly to the business rule and scales naturally from top three to top N.
- Window ranking usually requires sorting rows within each partition, so large departments can make memory, sort strategy, and indexes relevant to the execution plan.
Alternative solutions
Correlated count of distinct higher salaries
SELECT
d.name AS department,
e.name AS employee,
e.salary
FROM Employee e
JOIN Department d ON d.id = e.department_id
WHERE (
SELECT COUNT(DISTINCT e2.salary)
FROM Employee e2
WHERE e2.department_id = e.department_id
AND e2.salary > e.salary
) < 3
ORDER BY d.name, e.salary DESC, e.name;For each employee, the correlated subquery counts how many distinct salary values in the same department are higher.
If fewer than three distinct salary values are higher, the employee belongs to one of the top three distinct salary bands. Ties naturally produce the same count.
- This expresses the rule without window functions and can be useful when teaching the logic behind ranking.
- It is often less efficient and harder to read than a window function because the database must reason about a correlated aggregate for many outer rows.
Common mistakes
Using ROW_NUMBER() instead of DENSE_RANK()
ROW_NUMBER gives Ben and Chen different positions even though they have the same salary. One tied employee can consume a slot and push the third distinct salary out of the result.
Ranking all employees without PARTITION BY department_id
The ranking becomes global. A high salary in Engineering can change the rank of an employee in Sales even though the requirement says each department must be evaluated independently.
Filtering salary_rank in the same SELECT WHERE clause that defines it
Window functions are evaluated after WHERE in SQL’s logical processing order. Use a subquery or CTE to compute the rank first, then filter in an outer query.
Performance reasoning
- A useful supporting index for this access pattern can start with department_id and then salary, often with salary ordered descending when the engine can exploit that ordering. Exact usefulness depends on the database, data distribution, and broader workload.
- Window functions can require a sort per partition. Inspect the execution plan on real data rather than assuming the concise SQL is automatically cheap.
- Do not denormalize department names into Employee simply to avoid this small join. Keep the relational model correct first, then optimize measured bottlenecks with appropriate indexes or materialized reporting structures when justified.
Change the requirement
Change the requirement to return the top two distinct salaries for each department, then add a minimum salary threshold that applies before ranking. Explain why placing the threshold before versus after the window calculation can change the business meaning and result set.