← SQL roadmap

Topic 50 of 52 · Advanced

Select top two salary tiers per department

Return every employee in the two highest distinct salary tiers in each department, excluding NULL.

AdvancedMid-level · 3–5 yearsWindows

MySQL 8-compatible SELECT syntax; sample queries also execute in SQLite 3. Window functions require MySQL 8+.

Problem statement

Return every employee in the two highest distinct salary tiers in each department, excluding NULL.

Include tied top earners rather than forcing a fixed employee count.

Input: employees table.

Output: id, dept, salary ordered by dept, salary descending, id.

  • Ties must all remain.
  • Departments with one tier return that tier.

Sample 1

Input: A:100,100,80,70
Output: A:100,100,80

The top two distinct tiers include three employees.

Sample 2

Input: B:NULL,60
Output: B:60

Missing salary is excluded before ranking.

Brute-force solution

For each employee, count distinct salaries above it with a correlated subquery.

O(n²) without effective indexing or decorrelation.

CREATE TABLE employees(id INTEGER PRIMARY KEY, dept VARCHAR(10), salary INTEGER);
INSERT INTO employees VALUES (1,'A',100),(2,'A',100),(3,'A',80),(4,'B',60),(5,'B',NULL);
SELECT e.id,e.dept,e.salary FROM employees e WHERE e.salary IS NOT NULL AND (SELECT COUNT(DISTINCT x.salary) FROM employees x WHERE x.dept=e.dept AND x.salary>e.salary)<2 ORDER BY e.dept,e.salary DESC,e.id;
Optimized solution

DENSE_RANK computes distinct tiers per department once.

  1. Exclude NULL.
  2. Rank salary descending partitioned by dept.
  3. Filter rank <= 2 in an outer query.
CREATE TABLE employees(id INTEGER PRIMARY KEY, dept VARCHAR(10), salary INTEGER);
INSERT INTO employees VALUES (1,'A',100),(2,'A',100),(3,'A',80),(4,'B',60),(5,'B',NULL);
WITH r AS (SELECT id,dept,salary,DENSE_RANK() OVER(PARTITION BY dept ORDER BY salary DESC) tier FROM employees WHERE salary IS NOT NULL) SELECT id,dept,salary FROM r WHERE tier <= 2 ORDER BY dept,salary DESC,id;
Complexity analysis

Time: O(n log n) for partition sorting absent a useful ordering plan.

Auxiliary space: O(n) sorting/window workspace, engine-dependent.

Test cases
  • Tied tiers.
  • Departments with only NULL.
  • Single employee.
Expected IDs ordered: 1,2,3,4.
Common mistakes
  • Adding id into the DENSE_RANK order.
  • Filtering windows directly in MySQL WHERE.
Key takeaways
  • Use salary alone for tiers.
  • Sort the display separately.
  • Partition by the business group.
Follow-up challenges
  • How would exactly two employees differ?
  • What does RANK do after ties?
Practise SQL with the AI mentor

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.