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.
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.
- Exclude NULL.
- Rank salary descending partitioned by dept.
- 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?