SQL RANK and DENSE_RANK
intermediatewindow-functions
2 min read
What are RANK and DENSE_RANK?
Both assign a rank to each row based on the ORDER BY. The difference is how they handle ties:
RANK()— Assigns the same rank to ties, then skips: 1, 2, 2, 4.DENSE_RANK()— Assigns the same rank to ties but does not skip: 1, 2, 2, 3.
Syntax
RANK() OVER (ORDER BY column DESC)
DENSE_RANK() OVER (PARTITION BY col ORDER BY col DESC)When to Use
- Leaderboards and competition rankings
- Identifying top-N items with ties
- Salary rankings within departments
Key Points
- RANK Gaps — RANK leaves gaps after ties.
- DENSE_RANK No Gaps — DENSE_RANK never leaves gaps.
- ROW_NUMBER Comparison — ROW_NUMBER never produces ties; RANK and DENSE_RANK do.
- PARTITION BY — Partition to rank within groups.
- Determinism — RANK and DENSE_RANK are deterministic for tied values.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
SQL RANK and DENSE_RANK Challenge
Write a query that solve this task: rank all students from oldest to youngest.
Expected result
Each student with both RANK and DENSE_RANK values.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What are RANK and DENSE_RANK?
Initializing database...Each run starts from fresh sample data.
More Examples
Salary rank per department
Rank employees by salary within their department.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
When should I use RANK vs DENSE_RANK?
Use RANK for competition-style ranking (1st, 2nd, 2nd, 4th). Use DENSE_RANK for consecutive ranks (1st, 2nd, 2nd, 3rd).
Can I filter by RANK in a WHERE clause?
Not directly. Use a subquery or CTE: SELECT * FROM (...) sub WHERE rank <= 3.