1
Introduction: The Tie Dilemma
Suppose four students receive the following test scores:
Score 100 (Amit) • Score 100 (Rahul) • Score 90 (Priya) • Score 80 (Neha)
Both Amit and Rahul earned 100 points, so they tied for 1st place. But what rank should Priya (90 points) receive?
- Should Priya be in 3rd place because two students beat her? (Standard
RANK()behavior with gaps). - Or should Priya be in 2nd place because 90 is the second-highest score achieved? (
DENSE_RANK()behavior with NO gaps).
2
What Is DENSE_RANK()? (The Core Mental Model)
Definition
DENSE_RANK() assigns the identical rank to rows with equal values according to window ordering, while keeping the ranking sequence strictly consecutive without gaps (1, 1, 2, 3).SELECT
name,
score,
DENSE_RANK() OVER (
ORDER BY score DESC
) AS score_rank
FROM students;
name,
score,
DENSE_RANK() OVER (
ORDER BY score DESC
) AS score_rank
FROM students;
3
Basic Example: Student Test Scores
| Student Name | Score | DENSE_RANK() Output | Why? |
|---|---|---|---|
| Amit Verma | 100 | 1 | Highest score |
| Rahul Sharma | 100 | 1 | Tied for highest score |
| Priya Patel | 90 | 2 | Next distinct score (no rank skipped!) |
| Neha Singh | 80 | 3 | Consecutive next rank |
4
The Core Difference: RANK() vs. DENSE_RANK()
This is the single most important conceptual comparison in SQL ranking functions:
| Score | RANK() (With Gaps) | DENSE_RANK() (Gap-Free) | Key Distinction |
|---|---|---|---|
| 100 | 1 | 1 | Both assign 1 to the first score |
| 100 | 1 | 1 | Both assign 1 to the tied score |
| 90 | 3 (Skipped 2!) | 2 (Consecutive) | RANK() counts 2 rows ahead; DENSE_RANK() advances +1 |
| 80 | 4 | 3 | Maintains gap vs gap-free progression |
Diagram 1: RANK() (Gaps) vs DENSE_RANK() (Dense / Consecutive)
RANK()
1, 1, 2 3, 4
Skips rank numbers after tiesDENSE_RANK()
1, 1, 2, 3
Never skips rank numbers5
Three-Way Matrix: ROW_NUMBER vs. RANK vs. DENSE_RANK
Comparing all three SQL ranking functions across identical test data:
| Student | Score | ROW_NUMBER() | RANK() | DENSE_RANK() |
|---|---|---|---|---|
| Amit | 100 | 1 | 1 | 1 |
| Rahul | 100 | 2 (Unique ID) | 1 (Shared) | 1 (Shared) |
| Priya | 90 | 3 | 3 (Gap) | 2 (No Gap) |
| Neha | 80 | 4 | 4 | 3 |
6
DENSE_RANK() With PARTITION BY: Group-Level Restarts
Adding PARTITION BY department instructs SQL to restart the ranking sequence at 1 independently for each department:
SELECT
name,
department,
salary,
DENSE_RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS salary_rank
FROM employees;
name,
department,
salary,
DENSE_RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS salary_rank
FROM employees;
Diagram 2: Partitioned Group Rankings
Sales Department
• Rahul ($80k) ➔ Rank 1
• Amit ($80k) ➔ Rank 1
• Neha ($70k) ➔ Rank 2
• Amit ($80k) ➔ Rank 1
• Neha ($70k) ➔ Rank 2
HR Department
• Priya ($70k) ➔ Rank 1
• Sara ($60k) ➔ Rank 2
• Vikram ($60k) ➔ Rank 2
• Sara ($60k) ➔ Rank 2
• Vikram ($60k) ➔ Rank 2
7
How DENSE_RANK() Evaluates Multiple Duplicate Ties
DENSE_RANK() ranks by distinct values encountered in the order stream:
1000 ➔ 1st distinct value ➔ Rank 1
1000 ➔ 1st distinct value ➔ Rank 1
900 ➔ 2nd distinct value ➔ Rank 2
900 ➔ 2nd distinct value ➔ Rank 2
800 ➔ 3rd distinct value ➔ Rank 3
1000 ➔ 1st distinct value ➔ Rank 1
900 ➔ 2nd distinct value ➔ Rank 2
900 ➔ 2nd distinct value ➔ Rank 2
800 ➔ 3rd distinct value ➔ Rank 3
8
Top-N Analysis With DENSE_RANK()
In SQL, you cannot place a window function directly in a WHERE clause. Instead, encapsulate the ranking inside a CTE:
WITH ranked_products AS (
SELECT
product,
category,
sales,
DENSE_RANK() OVER (
PARTITION BY category
ORDER BY sales DESC
) AS sales_rank
FROM products
)
SELECT *
FROM ranked_products
WHERE sales_rank <= 3;
SELECT
product,
category,
sales,
DENSE_RANK() OVER (
PARTITION BY category
ORDER BY sales DESC
) AS sales_rank
FROM products
)
SELECT *
FROM ranked_products
WHERE sales_rank <= 3;
9
RANK() vs. DENSE_RANK() in Business Reporting
| Business Goal | Recommended Function | Reason |
|---|---|---|
| Podium / Tier Awards (e.g. Gold, Silver, Bronze tiers) | DENSE_RANK() | Guarantees that Silver and Bronze tiers exist even if two people tie for Gold. |
| Olympic-style Standings (Skip ranks after ties) | RANK() | If two runners tie for 1st, the next runner is officially 3rd. |
| Pagination / Unique Record Numbers | ROW_NUMBER() | Requires guaranteed unique indices without duplicates. |
10
Real-World Data Analytics Use Cases
- Executive Compensation Tiers: Finding the top 3 salary tiers within each department.
- Customer Loyalty Brackets: Segmenting customers into spending tiers (Tier 1, Tier 2, Tier 3).
- E-Commerce Top Sellers: Displaying the top 5 product sales positions per category without excluding tied products.
11
Common DENSE_RANK() Mistakes to Avoid
1. Direct WHERE Filtering
Writing WHERE DENSE_RANK() <= 3 in the same query. Window functions execute after WHERE, so a CTE is required.
2. Adding Extra ORDER BY Columns Unintentionally
Adding tie-breaker columns (e.g. ORDER BY score DESC, id ASC) breaks score ties into unique rows, defeating the purpose of DENSE_RANK.
3. Confusing PARTITION BY with GROUP BY
GROUP BY collapses multiple rows into one. PARTITION BY preserves individual rows while grouping rank calculations.
4. Expecting Exactly N Rows in Top-N
Assuming WHERE dense_rank <= 3 will return 3 rows. If 4 people tie for rank 1, all 4 are returned!
12
Practical Ranking Exercises
Exercise 1: Second Highest Salary Tier Per Department
WITH dept_salary_tiers AS (
SELECT
name, department, salary,
DENSE_RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS salary_tier
FROM employees
)
SELECT * FROM dept_salary_tiers
WHERE salary_tier = 2;
SELECT
name, department, salary,
DENSE_RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS salary_tier
FROM employees
)
SELECT * FROM dept_salary_tiers
WHERE salary_tier = 2;
⚡ Live Interactive SQL DENSE_RANK() Practice Lab
Test live tie handling, compare ROW_NUMBER vs RANK vs DENSE_RANK, and explore partitioned group ranking
Edit student scores in the inputs below to watch how DENSE_RANK() dynamically recalculates tied positions with zero gaps:
| Student | Subject | Editable Score (0–100) | DENSE_RANK() | Status |
|---|---|---|---|---|
| Amit Verma | Math | #1 | Rank #1 | |
| Rahul Sharma | Math | #1 | Rank #1 | |
| Priya Patel | Math | #2 | Rank #2 | |
| Neha Singh | Math | #3 | Rank #3 | |
| Rohan Gupta | Math | #3 | Rank #3 | |
| Vikram Joshi | Math | #4 | Rank #4 |
13
SQL DENSE_RANK() Best Practices
- Use DENSE_RANK() for Tiered Groupings: Ideal when tied members should share a rank and subsequent tiers must remain consecutive.
- Use CTEs for Top-N Filters: Wrap window functions in a CTE before applying
WHERE rank <= N. - Use PARTITION BY for Sub-Group Leaderboards: Reset rankings cleanly per department, category, or region.
- Avoid Unnecessary Tie-Breakers: Only add secondary
ORDER BYcolumns if you explicitly want unique non-tied rankings.
14
What You Should Know Now: Checklist
- ✓DENSE_RANK() Definition: Assigns equal ranks to ties with no gaps in the numbering sequence (1, 1, 2, 3).
- ✓DENSE_RANK vs RANK: RANK creates gaps (1, 1, 3, 4); DENSE_RANK is consecutive (1, 1, 2, 3).
- ✓DENSE_RANK vs ROW_NUMBER: ROW_NUMBER generates unique consecutive integers (1, 2, 3, 4) ignoring ties.
- ✓PARTITION BY: Restarts ranking at 1 inside each independent category or department partition.