DENSE_RANK(): Ranking Without Gaps
Rank employees by salary company-wide using DENSE_RANK, which never skips numbers after ties.
Overview
DENSE_RANK() is like RANK() but doesn't leave gaps after ties — if two
employees tie for 2nd, the next rank is 3, not 4. Use it when you care about the
density of ranking (e.g., "how many distinct salary tiers exist") rather than
exact positions.
The Query
Employees & Departments
Loading database engine...
Step-by-Step Breakdown
DENSE_RANK() OVER (ORDER BY salary DESC)— company-wide ranking by pay.- Tied salaries share a tier, and the next tier continues sequentially with no gap.
- No
PARTITION BYmeans the window is the entire result set.
RANK vs DENSE_RANK vs ROW_NUMBER
Ties → RANK skips (1,1,3), DENSE_RANK continues (1,1,2), ROW_NUMBER
splits them arbitrarily (1,2,3).
Variations
Dense-rank movies by budget within each genre:
Movies (Movies/Actors/Ratings)
Loading database engine...
Common Mistakes
- Expecting ROW_NUMBER behavior.
DENSE_RANKrepeats numbers on ties, so it's not unique — don't use it as a primary key substitute. - Confusing with RANK. Forgetting that
DENSE_RANKhas no gaps leads to off-by-one assumptions in downstream logic. - Filtering in WHERE. As with all window functions, filter on the rank via a wrapping query.