RANK() and DENSE_RANK() — Handling Ties

RANK() and DENSE_RANK() assign positional rankings to rows within a window — identical values receive identical ranks. The difference between them is in what happens to the numbers after a tie.

Understanding this distinction matters in any real-world ranking scenario: leaderboards, sales contests, cohort analysis, percentile reporting.

The difference in one example

SELECT
    p.name,
    SUM(oi.quantity * oi.unit_price)                                    AS revenue,
    RANK()       OVER (ORDER BY SUM(oi.quantity * oi.unit_price) DESC)  AS rank,
    DENSE_RANK() OVER (ORDER BY SUM(oi.quantity * oi.unit_price) DESC)  AS dense_rank
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.product_id, p.name
ORDER BY revenue DESC;

Suppose two products tie at rank 2:

     Product      | revenue | rank | dense_rank
------------------+---------+------+------------
 4K Monitor       | 4990.00 |    1 |          1
 Data Eng Course  | 2388.00 |    2 |          2
 React Deep Dive  | 2388.00 |    2 |          2   ← tie
 Mechanical KB    | 1935.00 |    4 |          3   ← RANK skips 3, DENSE_RANK doesn't
 Cloud Storage    | 1287.00 |    5 |          4

RANK(): tied rows share a rank, then the next rank jumps. Two rows at rank 2 → next rank is 4 (not 3). Like a sports podium where two athletes tie for silver — no bronze is awarded.
DENSE_RANK(): tied rows share a rank, but the next rank is always +1. Two rows at rank 2 → next rank is 3. No gaps.

When to use which:

Use RANK() when the number itself should reflect "how many rows are above me" (position in a true competition sense)
Use DENSE_RANK() when you want contiguous tier levels (e.g., pricing tiers, grade bands)

Ranking within partitions

The real power comes with PARTITION BY — each partition gets its own independent ranking:

-- Top revenue product in each category
WITH category_ranks AS (
    SELECT
        p.name,
        p.category,
        SUM(oi.quantity * oi.unit_price)                               AS revenue,
        RANK() OVER (
            PARTITION BY p.category
            ORDER BY SUM(oi.quantity * oi.unit_price) DESC
        )                                                              AS rank_in_cat
    FROM order_items oi
    JOIN products p ON p.product_id = oi.product_id
    GROUP BY p.product_id, p.name, p.category
)
SELECT name, category, revenue, rank_in_cat
FROM category_ranks
WHERE rank_in_cat = 1
ORDER BY category;

This retrieves the top-selling product in each category — including ties (if two products share the highest revenue in a category, both appear).

Ranking customers by spend — a full example

WITH customer_spend AS (
    SELECT
        o.customer_id,
        SUM(oi.quantity * oi.unit_price) AS total_spend
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY o.customer_id
),
ranked AS (
    SELECT
        c.name,
        c.country,
        cs.total_spend,
        RANK()       OVER (ORDER BY cs.total_spend DESC) AS global_rank,
        RANK()       OVER (PARTITION BY c.country
                           ORDER BY cs.total_spend DESC) AS country_rank,
        DENSE_RANK() OVER (ORDER BY cs.total_spend DESC) AS global_dense_rank
    FROM customer_spend cs
    JOIN customers c ON c.customer_id = cs.customer_id
)
SELECT name, country, total_spend, global_rank, country_rank, global_dense_rank
FROM ranked
ORDER BY global_rank, country;

This query simultaneously computes a global ranking and a per-country ranking for every customer in a single pass. Without window functions, this would require two separate subqueries.

Tier assignment with DENSE_RANK()

DENSE_RANK() is ideal for assigning discrete tiers — the sequential numbering makes tier labels natural:

Purchase this course to unlock the full lesson.

← Previous Next →