Course contentsShow
← Data Science
Lesson 1009 of 2,14526. Advanced SQLFree lesson

DENSE_RANK(): Ranking Without Gaps

Using DENSE_RANK() to assign rankings without gaps after ties (e.g., 1, 2, 2, 3).

DENSE_RANK(): Ranking Without Gaps

You'll learn how to assign rankings that remain consecutive even when ties occur, unlike RANK() which leaves gaps.

What Makes DENSE_RANK() Different?

Remember how RANK() handles ties by giving the same rank to tied values, then skipping numbers? If three people tie for 2nd place, RANK() assigns them all rank 2, but the next person gets rank 5 (skipping 3 and 4). DENSE_RANK() eliminates those gaps — the next person would simply get rank 3.

Think of it like medal standings in sports: if three athletes tie for silver, DENSE_RANK() ensures the next athlete still gets bronze (3rd place), not 5th.

Basic Syntax

SELECT 
    student_name,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
FROM exam_results;

If scores are 95, 90, 90, 85:

  • RANK() produces: 1, 2, 2, 4 (gap!)
  • DENSE_RANK() produces: 1, 2, 2, 3 (no gap!)

When to Use DENSE_RANK()

Use DENSE_RANK() when you need:

  • Consecutive rankings for categorization (like price tiers: budget, mid-range, premium)
  • Counting distinct performance levels rather than absolute positions
  • Rankings where gaps would confuse end users

Like RANK(), you can partition results:

SELECT 
    department,
    employee_name,
    salary,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

This gives each department its own gap-free ranking sequence.

Key Takeaway: DENSE_RANK() assigns rankings without gaps after ties, making it ideal when you need consecutive rank values (1, 2, 2, 3) rather than absolute positions with gaps (1, 2, 2, 4).