How ROW_NUMBER, RANK and DENSE_RANK Differ

All three are window functions that number rows in a given order. The difference shows up only on equal values:

• ROW_NUMBER ignores duplicates: every row gets its own number — 1, 2, 3, 4.
• RANK gives duplicates the same rank and assigns the next rank with a gap: 1, 1, 3, 4.
• DENSE_RANK gives duplicates the same rank without gaps: 1, 1, 2, 3.

The Whole Difference in One Query

Let's take delivery service orders and rank them by amount with all three functions at once. The data contains duplicates: two orders at 99.96 and three at 74.97 — exactly where the differences show:

MySQL 8.1
SELECT
    order_id,
    total_amount,
    ROW_NUMBER() OVER (ORDER BY total_amount DESC) AS row_num,
    RANK() OVER (ORDER BY total_amount DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY total_amount DESC) AS dense_rnk
FROM orders
ORDER BY total_amount DESC
LIMIT 8;

Reading row by row:

• Orders 20 and 26 cost the same. ROW_NUMBER still handed them numbers 1 and 2 — and which one comes first is up to the DBMS. RANK and DENSE_RANK honestly gave both rank 1.
• Order 218 is next by amount. RANK gave it 3: the two orders above "ate" places 1 and 2. DENSE_RANK gave it 2: it never leaves gaps.
• The trio of 74.97 orders repeats the picture: RANK — 6, 6, 6 with 9 coming next; DENSE_RANK — 5, 5, 5 with 6 coming next.

An easy mnemonic: RANK counts like sports — after two gold medals comes bronze, there is no second place. DENSE_RANK ("dense") is like level numbering: the next value is always exactly one bigger.

A function ranks the values 100, 100, 90 in descending order. What ranks does DENSE_RANK produce?

When to Use Which

ROW_NUMBER: Top-N per Group

The most common practical task for window functions is "pick the best item in every category". Let's find the most profitable product of each category:

MySQL 8.1
WITH ranked AS (
    SELECT
        p.category,
        p.name,
        SUM(o.total_amount) AS revenue,
        ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY SUM(o.total_amount) DESC) AS rn
    FROM orders o
    JOIN products p ON p.product_id = o.product_id
    GROUP BY p.category, p.name
)
SELECT category, name, revenue
FROM ranked
WHERE rn = 1
ORDER BY category;

PARTITION BY category restarts the numbering in each category, so rn = 1 is the leader of its group. Change the condition to rn <= 3 and you get a top-3.

What matters here is that ROW_NUMBER guarantees exactly one row per group. With equal revenue RANK would return two "leaders" — sometimes that is what you want, but more often it breaks a report designed for one row.

You need exactly one most expensive order per customer, even if a customer has two orders with the same maximum amount. Which function fits?

ROW_NUMBER: Deduplication

The second classic scenario is removing duplicates while keeping one record each. We number rows within groups of equal values and drop everything past the first:

MySQL 8.1
WITH numbered AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
    FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM numbered WHERE rn > 1);

For each email the earliest record survives (ORDER BY created_at), the rest are deleted.

RANK and DENSE_RANK: Places and Levels

Pick RANK where sports-style placing logic applies: leaderboards, contests, "which place did the athlete take". Pick DENSE_RANK where you need gap-free levels: pay grades, threshold grouping, "the N-th largest value".

Bonus: NTILE Splits Rows into Buckets

From the same family of ranking functions comes NTILE(n): it splits rows into n roughly equal parts and returns the part number. One query is enough to split customers into spending quartiles:

MySQL 8.1
SELECT
    user_id,
    SUM(total_amount) AS spent,
    NTILE(4) OVER (ORDER BY SUM(total_amount) DESC) AS quartile
FROM orders
GROUP BY user_id
ORDER BY spent DESC
LIMIT 5;

Customers with quartile = 1 are the top quarter by spending — a ready-made segment for a loyalty program.

The Interview Task: The Second-Highest Amount

"Find the second-highest salary" is a question that has wandered through interviews for decades. With DENSE_RANK it solves transparently:

MySQL 8.1
WITH ranked AS (
    SELECT order_id, total_amount, DENSE_RANK() OVER (ORDER BY total_amount DESC) AS rnk
    FROM orders
)
SELECT DISTINCT total_amount
FROM ranked
WHERE rnk = 2;

Why exactly DENSE_RANK? Our top two amounts are both 99.96. RANK would give them rank 1 and the next amount rank 3, so the condition rnk = 2 would find nothing. DENSE_RANK leaves no gaps and assigns 91.81 the rank 2 — which is "the second highest" as the interviewer means it.

What's Next

• how window functions, OVER and PARTITION BY work — in the lesson Window functions;
• the kinds of window functions and their syntax — in the lesson Types of window functions;
• ordering inside a window — in the lesson Sorting in window functions;
• ranking tasks come up regularly in the trainer and in interview questions.