RANK vs DENSE_RANK vs ROW_NUMBER in SQL

This is one of the most common SQL interview questions in India, and one of the most common sources of subtly wrong reports. RANK(), DENSE_RANK(), and ROW_NUMBER() all assign positions to rows, and on data with no ties they return identical results - which lulls people into thinking they are interchangeable. They are not. The moment two rows tie, they diverge, and choosing wrong gives you the wrong answer. This guide makes the difference crystal clear.

The one-sentence summary

• ROW_NUMBER() - every row gets a unique number; ties are broken arbitrarily.
• RANK() - tied rows share a rank, and the next rank skips (gaps appear).
• DENSE_RANK() - tied rows share a rank, and the next rank does not skip (no gaps).

All three are window functions and require an ORDER BY inside OVER.

See it with real data

Imagine Flipkart seller revenue (in ₹ lakh) for the month:

Run all three at once:

SELECT
    seller,
    revenue,
    ROW_NUMBER() OVER (ORDER BY revenue DESC) AS row_num,
    RANK()       OVER (ORDER BY revenue DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY revenue DESC) AS dense_rnk
FROM seller_revenue;

Result:

Look at sellers A and B (both 50 lakh):

• ROW_NUMBER gave them 1 and 2 - it refuses ties, so it picked an order between them (which one gets 1 is non-deterministic unless you add a tie-breaker).
• RANK gave both 1, then jumped to 3 for seller C - it skipped 2 because two rows occupied rank 1.
• DENSE_RANK gave both 1, then 2 for seller C - no gap.

That single difference - whether ranks skip after a tie - is the whole topic.

When to use which

Use ROW_NUMBER for deduplication and "exactly one per group"

When you need precisely one row and do not care that ties are broken arbitrarily, ROW_NUMBER is the tool. Classic case: a customers table has duplicate rows and you want to keep only the latest record per email.

WITH ranked AS (
    SELECT
        customer_id,
        email,
        updated_at,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY updated_at DESC
        ) AS rn
    FROM customers
)
SELECT customer_id, email, updated_at
FROM ranked
WHERE rn = 1;

rn = 1 keeps exactly one row per email - the most recently updated. RANK or DENSE_RANK would be wrong here: if two rows share the same updated_at, they would both get rank 1 and you would keep duplicates.

Use RANK for genuine competition ranking

When the business semantics are "Olympic style" - two gold medals means no silver - use RANK. If two Zomato restaurants tie for the top rating, both are "rank 1" and the next is "rank 3." The skip is meaningful: it tells you how many competitors are ahead.

Use DENSE_RANK for "top N distinct values"

When you want the top 3 distinct price points, or top 5 distinct revenue tiers, DENSE_RANK is right because it counts distinct values, not rows.

SELECT seller, revenue
FROM (
    SELECT
        seller,
        revenue,
        DENSE_RANK() OVER (ORDER BY revenue DESC) AS dr
    FROM seller_revenue
) t
WHERE dr <= 3;

With the data above, dr <= 3 returns sellers A, B (50), C (40), and D (30) - the top three distinct revenue levels. Using RANK here, the gap could exclude a value you meant to include; using ROW_NUMBER would arbitrarily cut a tied seller.

Top-N-per-group: the pattern interviewers love

"Give me the top 2 selling products in each category." This is a PARTITION BY plus a ranking function:

SELECT category, product_name, revenue
FROM (
    SELECT
        category,
        product_name,
        SUM(order_value) AS revenue,
        DENSE_RANK() OVER (
            PARTITION BY category
            ORDER BY SUM(order_value) DESC
        ) AS dr
    FROM orders
    GROUP BY category, product_name
) ranked
WHERE dr <= 2;

Which ranking function? It depends on the requirement:

• "Top 2 products, and if there is a tie keep both" -> DENSE_RANK (or RANK).
• "Exactly 2 products per category, no more" -> ROW_NUMBER with a deterministic tie-breaker like ORDER BY revenue DESC, product_name.

Always clarify the tie rule in an interview before you write the query - that is what they are really testing.

Critical pitfalls

• You cannot filter on the rank in WHERE. Window functions run after WHERE, so wrap them in a subquery or CTE and filter there. This trips up almost every beginner.
• ROW_NUMBER without a tie-breaker is non-deterministic. If your ORDER BY has duplicates, the row that gets 1 can change between runs. Add a unique column to the ORDER BY for stable dedup.
• Choosing RANK when you meant DENSE_RANK. A "top 3" filter with RANK can silently return fewer than three distinct values when ties create gaps. If you want three distinct levels, use DENSE_RANK.
• Forgetting PARTITION BY. Without it, the ranking is global, not per-group - a very common bug in top-N-per-category queries.
• NULLs in ORDER BY. Different databases sort NULLs first or last by default, which shifts ranks. Be explicit with NULLS LAST where your dialect supports it.

The mental model to remember

Picture a leaderboard. ROW_NUMBER hands out unique jersey numbers no matter what. RANK gives Olympic medals - ties share a place and the next place skips. DENSE_RANK is a "tier list" - ties share a tier and tiers never skip. Match the function to the story the business is telling, and you will never pick the wrong one again.

Related: SQL + Power BI Challenge for Data Analysts · Practise SQL problems

Don't just read. Prove your skill on DevWithData.