ROW_NUMBER vs RANK vs DENSE_RANK: What’s the Difference?

ROW_NUMBER, RANK and DENSE_RANK assign positions to rows within a window. They do not collapse rows like GROUP BY. The key difference is what happens when two or more rows have the same ordering value.

One dataset, three different answers

Imagine a team leaderboard based on completed sales. Alex and Sam have the same sales total, as do Jo and Lee. We will rank everyone from highest to lowest using the same five rows. These examples use PostgreSQL syntax; the window-function logic also applies to Athena and Trino.

WITH sales (person_id, person, total_sales) AS (
    VALUES
        (1, 'Alex', 100),
        (2, 'Sam', 100),
        (3, 'Jo', 80),
        (4, 'Lee', 80),
        (5, 'Pat', 60)
)
SELECT
    person,
    total_sales,
    ROW_NUMBER() OVER (
        ORDER BY total_sales DESC, person_id
    ) AS row_num,
    RANK() OVER (
        ORDER BY total_sales DESC
    ) AS sales_rank,
    DENSE_RANK() OVER (
        ORDER BY total_sales DESC
    ) AS dense_sales_rank
FROM sales
ORDER BY total_sales DESC,

WITH sales (person_id, person, total_sales) AS (
    VALUES
        (1, 'Alex', 100),
        (2, 'Sam', 100),
        (3, 'Jo', 80),
        (4, 'Lee', 80),
        (5, 'Pat', 60)
)
SELECT
    person,
    total_sales,
    ROW_NUMBER() OVER (
        ORDER BY total_sales DESC, person_id
    ) AS row_num,
    RANK() OVER (
        ORDER BY total_sales DESC
    ) AS sales_rank,
    DENSE_RANK() OVER (
        ORDER BY total_sales DESC
    ) AS dense_sales_rank
FROM sales
ORDER BY total_sales DESC,

WITH sales (person_id, person, total_sales) AS (
    VALUES
        (1, 'Alex', 100),
        (2, 'Sam', 100),
        (3, 'Jo', 80),
        (4, 'Lee', 80),
        (5, 'Pat', 60)
)
SELECT
    person,
    total_sales,
    ROW_NUMBER() OVER (
        ORDER BY total_sales DESC, person_id
    ) AS row_num,
    RANK() OVER (
        ORDER BY total_sales DESC
    ) AS sales_rank,
    DENSE_RANK() OVER (
        ORDER BY total_sales DESC
    ) AS dense_sales_rank
FROM sales
ORDER BY total_sales DESC,

ROW_NUMBER includes person_id as a tie-breaker so its output is reproducible. RANK and DENSE_RANK intentionally order only by total_sales, preserving ties between people with equal sales. The final ORDER BY controls display order, not the ranks themselves.

In the results below, RN means ROW_NUMBER, R means RANK and DR means DENSE_RANK. Each row shows all three positions for the same person.

ROW_NUMBER: a unique position for every row

ROW_NUMBER gives each row a different number, starting at 1. Even if values tie, rows receive separate positions. In this example Alex is 1 and Sam is 2 because person_id breaks the tie. Without a unique tie-breaker, the database can assign tied rows in either order; that is not a reliable way to choose a winner.

Use ROW_NUMBER when you need one latest record per customer, a deterministic deduplication rule or exactly N records per group. “Exactly N” assumes the group contains at least N rows. The rule for choosing among ties must reflect the business requirement, not merely make the query run.

Example: the latest event for each customer

WITH numbered AS (
    SELECT
        customer_id,
        event_id,
        event_time,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY event_time DESC, event_id DESC
        ) AS rn
    FROM customer_events
)
SELECT customer_id, event_id, event_time
FROM numbered
WHERE rn = 1

WITH numbered AS (
    SELECT
        customer_id,
        event_id,
        event_time,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY event_time DESC, event_id DESC
        ) AS rn
    FROM customer_events
)
SELECT customer_id, event_id, event_time
FROM numbered
WHERE rn = 1

WITH numbered AS (
    SELECT
        customer_id,
        event_id,
        event_time,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY event_time DESC, event_id DESC
        ) AS rn
    FROM customer_events
)
SELECT customer_id, event_id, event_time
FROM numbered
WHERE rn = 1

PARTITION BY restarts numbering for each customer. A unique event_id makes the ordering deterministic when timestamps tie. It is only a tie-breaker here: a larger ID is not necessarily a later event unless your system guarantees that. The CTE lets us filter the window result in a subsequent query step.

RANK: ties share a position, with gaps afterwards

RANK assigns the same position to rows with equal ordering values. The next rank reflects how many rows came before it. Alex and Sam are both rank 1; Jo and Lee are rank 3 because two rows precede them. Pat is rank 5. The sequence is 1, 1, 3, 3, 5.

Use RANK for competition-style leaderboards and questions where a position includes everyone tied at that position. Filtering sales_rank <= 3 returns four people in our example. Filtering sales_rank <= 2 returns only Alex and Sam because rank 2 does not exist. That is different from asking for the top two distinct sales totals.

DENSE_RANK: ties share a position, without gaps

DENSE_RANK gives equal values the same rank, but advances by one for each new ordering value. Alex and Sam are rank 1, Jo and Lee are rank 2, and Pat is rank 3. The sequence is 1, 1, 2, 2, 3.

Use DENSE_RANK when you need the top N distinct values and all records matching them. For example, the second-highest distinct salary is dense rank 2. In our sales dataset, dense_sales_rank <= 2 returns Alex, Sam, Jo and Lee: everyone at the two highest distinct sales totals.

Choosing the right function in real work

Start by clarifying what “top three” means. Is it three rows, three competition positions or three distinct values? Those are three different requirements. On this dataset, ROW_NUMBER <= 3 returns Alex, Sam and Jo; RANK <= 3 returns Alex, Sam, Jo and Lee; DENSE_RANK <= 3 returns all five people.

-- Reuse the sales CTE from the first example.
WITH sales (person_id, person, total_sales) AS (
    VALUES
        (1, 'Alex', 100),
        (2, 'Sam', 100),
        (3, 'Jo', 80),
        (4, 'Lee', 80),
        (5, 'Pat', 60)
), ranked AS (
    SELECT
        person,
        total_sales,
        DENSE_RANK() OVER (
            ORDER BY total_sales DESC
        ) AS sales_band
    FROM sales
)
SELECT person, total_sales
FROM ranked
WHERE sales_band <= 2
ORDER BY total_sales DESC,

-- Reuse the sales CTE from the first example.
WITH sales (person_id, person, total_sales) AS (
    VALUES
        (1, 'Alex', 100),
        (2, 'Sam', 100),
        (3, 'Jo', 80),
        (4, 'Lee', 80),
        (5, 'Pat', 60)
), ranked AS (
    SELECT
        person,
        total_sales,
        DENSE_RANK() OVER (
            ORDER BY total_sales DESC
        ) AS sales_band
    FROM sales
)
SELECT person, total_sales
FROM ranked
WHERE sales_band <= 2
ORDER BY total_sales DESC,

-- Reuse the sales CTE from the first example.
WITH sales (person_id, person, total_sales) AS (
    VALUES
        (1, 'Alex', 100),
        (2, 'Sam', 100),
        (3, 'Jo', 80),
        (4, 'Lee', 80),
        (5, 'Pat', 60)
), ranked AS (
    SELECT
        person,
        total_sales,
        DENSE_RANK() OVER (
            ORDER BY total_sales DESC
        ) AS sales_band
    FROM sales
)
SELECT person, total_sales
FROM ranked
WHERE sales_band <= 2
ORDER BY total_sales DESC,

If you need a separate leaderboard for each region or department, add PARTITION BY region or department inside OVER. All three functions restart at 1 in each partition. Be explicit about null handling too: default null ordering varies by database, so decide whether missing sales or timestamps should be excluded or ranked last.

A common interview trap: accidentally removing ties

Rows are peers only when every expression in the window ORDER BY compares equally. Adding a unique person_id to RANK() OVER (ORDER BY total_sales DESC, person_id) removes the sales ties. All five people would then receive different ranks. Use the business measure alone when equal measures should share a rank; add a unique tie-breaker to ROW_NUMBER when selecting individual rows.

Also distinguish window ordering from output ordering. ORDER BY inside OVER determines the calculation. An outer ORDER BY determines how the final rows are presented. One is not a substitute for the other.

Interview takeaway

ROW_NUMBER gives every row a unique position. RANK keeps ties and leaves gaps. DENSE_RANK keeps ties without gaps. Explain that using 100, 100, 80, 80, 60: the results are 1, 2, 3, 4, 5; 1, 1, 3, 3, 5; and 1, 1, 2, 2, 3. Then connect the choice to the requirement and explain how you would handle ties deterministically.