Topic 44 of 52 · Advanced
How do ties and window frames change a top-earner report?
ROW_NUMBER selects a fixed number of rows, RANK leaves gaps after ties, and DENSE_RANK selects distinct value tiers without gaps. Top three employees and top three salary levels are different requirements.
MySQL 8-compatible SELECT syntax; sample queries also execute in SQLite 3. Window functions require MySQL 8+.
Detailed answer
ROW_NUMBER selects a fixed number of rows, RANK leaves gaps after ties, and DENSE_RANK selects distinct value tiers without gaps. Top three employees and top three salary levels are different requirements.
ROW_NUMBER needs a stable secondary ordering key for deterministic selection. Adding that key to DENSE_RANK would break salary ties and change the result.
For running totals, explicitly use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The default ordered RANGE frame includes peers with the same ordering value.
Internal working
Windows partition rows, order within partitions, then evaluate a frame. Filtering window output normally requires a CTE or derived table in MySQL; BigQuery also supports QUALIFY.
Practical scenario
Two employees earn the same salary. HR asks to include all employees in the highest three salary tiers, even when more than three employees qualify.
WITH ranked AS (SELECT id, dept, salary, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) r FROM employees)
SELECT * FROM ranked WHERE r <= 3;Common mistakes
- Adding id to a salary-tier ranking.
- Assuming RANGE and ROWS are equivalent with ties.
Key takeaways
- Clarify tie handling.
- Specify running frames.
- Use a stable tie-breaker for row selection.
Follow-up challenges
- How would you return exactly three employees?
- What is LAST_VALUE's default-frame trap?