SQL ROW_NUMBER, RANK, and DENSE_RANK Guide

SQL ROW_NUMBER, RANK, and DENSE_RANK are three of the most important window functions because they help you answer a very common type of question:

how does each row compare to other rows in an ordered set?

That matters in real work much more often than people think.

You may need to:

• rank employees by salary
• find the top 3 products in each category
• assign leaderboard positions
• pick the latest order per customer
• deduplicate repeated records
• identify tied scores
• or compare results inside each region, department, tenant, or month

These are all ranking problems.

At first, ROW_NUMBER, RANK, and DENSE_RANK look similar because they all assign numbers to rows. But the moment ties appear, they behave very differently.

That is exactly why they matter.

Choosing the wrong ranking function can produce:

• the wrong leaderboard
• the wrong top-N results
• incorrect deduplication
• or confusing reports where tied values behave differently from what the business expects

This guide explains SQL ROW_NUMBER, RANK, and DENSE_RANK clearly, including:

• what each one does
• how ties work
• how PARTITION BY changes the result
• how ORDER BY controls the ranking
• practical use cases
• and the common mistakes that cause incorrect ranking logic

Why ranking functions matter

A lot of SQL questions are not only about filtering rows. They are about ordering rows relative to one another.

Examples:

• who is the highest-paid employee in each department?
• what is each product’s rank by revenue?
• which order is the latest for each customer?
• which user action came first, second, and third in a session?
• which rows are duplicates if I keep only the newest one?
• which students tied for first place?
• what is the top 5 by category, not just overall?

These are not simple GROUP BY questions. They are sequence and ranking questions.

That is why ranking window functions are so useful.

They let you:

• keep row-level detail
• while also giving each row a position inside an ordered group

That combination is extremely powerful.

The most important rule

Before anything else, remember this:

The difference between ROW_NUMBER, RANK, and DENSE_RANK only becomes visible when ties exist.

That is the single most important rule in this topic.

If all values are distinct, they often appear to behave the same way.

But the moment two rows have the same ordering value:

• ROW_NUMBER still gives them different numbers
• RANK gives them the same rank and skips the next rank
• DENSE_RANK gives them the same rank and does not skip the next rank

That tie behavior is the heart of the whole subject.

If you remember only one thing, remember that.

What these functions are for

All three functions are ranking window functions. They are used with an OVER (...) clause.

That means they do not collapse rows like GROUP BY does. Instead, they assign ranking information to each row while preserving the result set.

That is why they are so useful for:

• top-N queries
• leaderboards
• deduplication
• latest-row-per-group logic
• and sequence analysis

They are ranking tools, not aggregation tools.

What ROW_NUMBER does

ROW_NUMBER() gives each row a unique sequence number inside the defined window.

That means:

• no two rows get the same row number
• even if the ordering values tie

This is extremely useful when you need:

• exactly one winner
• exactly one latest row
• exactly one first row
• or a deterministic sequence through rows

A useful way to think about it is:

ROW_NUMBER forces a unique order.

That is why it is often the best choice for:

• deduplication
• picking one row per group
• pagination-like row sequencing
• and “latest row per entity” queries

What RANK does

RANK() gives tied rows the same rank.

But after a tie, it skips the next rank number.

That means if two rows tie for first place, the next row gets rank 3, not rank 2.

This is often called:

• competition ranking
• or standard ranking with gaps

A useful way to think about it is:

RANK respects ties and leaves gaps after them.

This is useful when the business meaning wants tied rows to occupy the same position and later positions to reflect how many rows were ahead.

What DENSE_RANK does

DENSE_RANK() also gives tied rows the same rank.

But unlike RANK, it does not leave gaps after ties.

That means if two rows tie for first place, the next row gets rank 2, not rank 3.

A useful way to think about it is:

DENSE_RANK respects ties but keeps ranks compact.

This is useful when you want:

• first distinct value = rank 1
• second distinct value = rank 2
• third distinct value = rank 3

without skipping numbers.

The easiest way to remember the difference

Here is the simplest memory trick:

• ROW_NUMBER = every row gets its own number
• RANK = ties share rank, gaps appear
• DENSE_RANK = ties share rank, no gaps appear

That one summary usually solves most confusion.

A simple example with scores

Suppose you have these scores:

player_name	score
Alice	100
Bob	100
Cara	90
Dan	80

Now apply all three ranking functions ordered by score descending.

ROW_NUMBER result

player_name	score	row_number
Alice	100	1
Bob	100	2
Cara	90	3
Dan	80	4

RANK result

player_name	score	rank
Alice	100	1
Bob	100	1
Cara	90	3
Dan	80	4

DENSE_RANK result

player_name	score	dense_rank
Alice	100	1
Bob	100	1
Cara	90	2
Dan	80	3

This example is the fastest way to see the real difference.

Why ORDER BY matters so much

These functions depend completely on the ordering defined inside the window.

For example:

ROW_NUMBER() OVER (ORDER BY score DESC)

means:

• assign row numbers based on score from highest to lowest

If the ORDER BY changes, the ranking changes.

That is why ORDER BY is not optional in practical ranking logic. It defines:

• what “first” means
• what “higher” means
• and how ties are detected

Without a meaningful ordering, the rank has no useful business meaning.

Why tie-breakers matter for ROW_NUMBER

Because ROW_NUMBER forces uniqueness, ties must still be broken somehow.

Suppose two employees have the same salary. If you write:

ROW_NUMBER() OVER (ORDER BY salary DESC)

then the database may assign row 1 and row 2 between them, but unless there is a stable tie-breaker, that relative order may not be logically deterministic.

A better pattern is often:

ROW_NUMBER() OVER (ORDER BY salary DESC, employee_id ASC)

Now the ranking is more stable because tied salaries are resolved by employee ID.

This matters a lot when you use ROW_NUMBER to choose:

• the latest row
• the first row
• the canonical record
• or one winner per group

PARTITION BY changes the ranking scope

PARTITION BY tells SQL to restart the ranking inside separate groups.

Without PARTITION BY, the whole result set is ranked together.

With PARTITION BY, each group gets its own independent ranking.

This is one of the most important ranking concepts.

For example:

• rank employees by salary within each department
• rank orders by date within each customer
• rank products by revenue within each category

That is what PARTITION BY is for.

Example: rank employees by salary within department

Suppose you have:

employee_name	department	salary
Alice	Finance	70000
Bob	Finance	65000
Cara	Finance	65000
Dan	Marketing	80000
Eva	Marketing	60000

Now use:

SELECT
    employee_name,
    department,
    salary,
    RANK() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

This means:

• restart the ranking inside each department
• highest salary gets rank 1 within that department

That is one of the most common real-world ranking patterns.

Without PARTITION BY, the rank is global

If you wrote:

RANK() OVER (ORDER BY salary DESC)

then all employees across all departments would be ranked together.

That may be correct for:

• company-wide leaderboard

But it would be wrong for:

• per-department ranking

That is why the ranking scope matters so much.

A very useful question to ask is:

• should the ranking restart for each group?

If yes, you probably need PARTITION BY.

Basic syntax pattern

All three functions follow this general pattern:

FUNCTION_NAME() OVER (
    PARTITION BY ...
    ORDER BY ...
)

Where:

• PARTITION BY is optional
• ORDER BY is essential for meaningful ranking

Examples:

ROW_NUMBER() OVER (ORDER BY created_at DESC)

RANK() OVER (PARTITION BY department ORDER BY salary DESC)

DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC)

This syntax is the core of ranking queries.

When to use ROW_NUMBER

Use ROW_NUMBER when you need exactly one row in each ordered position.

This is especially useful for:

1. Deduplication

Keep only one row per duplicate group.

2. Latest row per group

Keep the newest order per customer, newest event per user, latest status per ticket.

3. Stable sequencing

Assign each row a unique position in an ordered list.

4. Pagination-like numbering

Create explicit row positions in ordered outputs.

The key idea is:

• ROW_NUMBER is best when you want one row, not shared positions

Example: latest order per customer with ROW_NUMBER

WITH ranked_orders AS (
    SELECT
        o.*,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC, order_id DESC
        ) AS rn
    FROM orders o
)
SELECT *
FROM ranked_orders
WHERE rn = 1;

This is one of the most important practical SQL patterns.

It says:

• rank each customer’s orders from newest to oldest
• keep only row number 1

This returns the latest order per customer.

ROW_NUMBER is perfect here because you want exactly one row.

Why ROW_NUMBER is better than RANK for deduplication

Suppose duplicate rows exist by email, and you want to keep only one.

ROW_NUMBER is the right tool because it guarantees:

• one row gets 1
• one row gets 2
• one row gets 3

That makes it easy to keep:

• rn = 1

If you used RANK instead, tied values might share the same rank, which is not helpful when you need exactly one survivor.

This is one of the clearest practical boundaries:

• use ROW_NUMBER when you need exactly one chosen row

When to use RANK

Use RANK when ties should share the same position and later positions should reflect the skipped positions caused by those ties.

This is useful in cases like:

• competition ranking
• leaderboard positions
• tournament results
• “who is first, second, third” where ties affect the next visible position

Example: if two rows tie for first, the next row should be third.

That is a RANK problem.

Example: leaderboard with RANK

SELECT
    player_name,
    score,
    RANK() OVER (
        ORDER BY score DESC
    ) AS leaderboard_rank
FROM player_scores;

This gives tied players the same rank.

If two players tie for first, the next player is rank 3.

That often matches real leaderboard or competition logic.

When to use DENSE_RANK

Use DENSE_RANK when ties should share the same position, but you want the rank values to stay compact.

This is useful when:

• you care about distinct value levels
• you want first, second, third distinct score bands
• you want grouped rank categories without gaps
• or you want to rank distinct revenue levels, salary levels, or score levels

Example: if two rows tie for first, the next row should be second.

That is a DENSE_RANK problem.

Example: salary bands with DENSE_RANK

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

This gives the same rank to tied salaries and keeps the next distinct salary level as the next integer.

That can be useful when ranking distinct salary tiers rather than competition positions.

Practical comparison: top 3 per group

A very common task is:

• top 3 products by revenue in each category

The ranking function changes the meaning.

Using ROW_NUMBER

You get exactly 3 rows per category, even if ties exist.

Using RANK

You may get more than 3 rows if ties occur before rank 3, and gaps may appear.

Using DENSE_RANK

You may also get more than 3 rows if ties exist, but the distinct rank levels stay compact.

This is why the ranking choice affects both:

• row count
• and business meaning

That is a very important practical lesson.

Example: top 3 products per category with ROW_NUMBER

WITH ranked_products AS (
    SELECT
        product_id,
        product_name,
        category,
        revenue,
        ROW_NUMBER() OVER (
            PARTITION BY category
            ORDER BY revenue DESC, product_id ASC
        ) AS rn
    FROM product_revenue
)
SELECT *
FROM ranked_products
WHERE rn <= 3;

This guarantees:

• at most 3 rows per category

That makes it useful when the business wants:

• exactly 3 rows per category

Example: top 3 products per category with RANK

WITH ranked_products AS (
    SELECT
        product_id,
        product_name,
        category,
        revenue,
        RANK() OVER (
            PARTITION BY category
            ORDER BY revenue DESC
        ) AS rnk
    FROM product_revenue
)
SELECT *
FROM ranked_products
WHERE rnk <= 3;

Now ties matter.

If two products tie for first and another product is second distinct value, the next rank may skip.

This may return:

• more than 3 rows in some categories

That is often correct when ties should be preserved fairly.

Example: top 3 distinct revenue levels with DENSE_RANK

WITH ranked_products AS (
    SELECT
        product_id,
        product_name,
        category,
        revenue,
        DENSE_RANK() OVER (
            PARTITION BY category
            ORDER BY revenue DESC
        ) AS drnk
    FROM product_revenue
)
SELECT *
FROM ranked_products
WHERE drnk <= 3;

This returns rows in the top 3 distinct revenue levels per category.

That is a slightly different business meaning from both ROW_NUMBER and RANK.

This is exactly why understanding the functions deeply matters.

Common real-world use cases

Here are the most common patterns.

1. Deduplicating records

Use ROW_NUMBER to keep one row per duplicate group.

2. Latest row per entity

Use ROW_NUMBER to keep the newest row per customer, user, ticket, or device.

3. Competition leaderboard

Use RANK.

4. Distinct value tiers

Use DENSE_RANK.

5. Top-N per group

Use any of the three, depending on whether you want:

• exactly N rows
• tied ranks with gaps
• tied ranks without gaps

This is where most production ranking queries come from.

Why stable ORDER BY matters even more than people expect

A ranking query is only as good as its sort definition.

Suppose you write:

ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY order_date DESC
)

If several rows share the same order_date, which one gets row number 1?

Without a tie-breaker, the result may not be fully deterministic in business terms.

A stronger pattern is:

ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY order_date DESC, order_id DESC
)

This creates a stable order.

That is one of the most important ranking best practices:

• always think about ties in the sort itself, not only in the function choice

Common mistake: using ROW_NUMBER when ties should share a position

This is one of the biggest ranking mistakes.

Suppose two students got the same score and should both be rank 1.

If you use ROW_NUMBER, one will get 1 and one will get 2.

That is wrong if the business meaning is:

• tied rank

So a strong rule is:

• do not use ROW_NUMBER when tied rows should visibly share the same ranking position

Use RANK or DENSE_RANK instead.

Common mistake: using RANK when you really want exactly one row

This happens often in latest-row-per-group queries.

Suppose two rows share the same latest timestamp. If you use RANK, both rows may get rank 1.

That means filtering to:

• rank = 1

returns more than one row.

If the business expects:

• exactly one row

then ROW_NUMBER is usually the correct choice.

This is a very common production bug pattern.

Common mistake: not understanding gaps in RANK

Developers often use RANK and then get confused when ranks go:

• 1
• 1
• 3

That is not a bug. That is how RANK works.

If the business expects:

• 1
• 1
• 2

then the correct function is DENSE_RANK.

This is one of the clearest differences to understand.

Common mistake: forgetting PARTITION BY

If the ranking should restart by:

• department
• customer
• region
• category
• tenant
• or team

then forgetting PARTITION BY creates a global ranking instead.

That can silently produce incorrect business results.

Always ask:

• should this rank restart inside groups?

If yes, add PARTITION BY.

Practical examples

Example 1: unique row numbering

SELECT
    employee_name,
    salary,
    ROW_NUMBER() OVER (
        ORDER BY salary DESC, employee_id ASC
    ) AS row_num
FROM employees;

Use when:

• every row needs a unique sequence

Example 2: competition ranking

SELECT
    employee_name,
    salary,
    RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

Use when:

• ties should share rank
• later ranks should have gaps

Example 3: dense ranking

SELECT
    employee_name,
    salary,
    DENSE_RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_dense_rank
FROM employees;

Use when:

• ties should share rank
• no gaps should appear afterward

Example 4: latest row per customer

WITH ranked_orders AS (
    SELECT
        o.*,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC, order_id DESC
        ) AS rn
    FROM orders o
)
SELECT *
FROM ranked_orders
WHERE rn = 1;

Use when:

• exactly one latest row is required

Example 5: top 3 salaries per department with tied ranks

WITH ranked_employees AS (
    SELECT
        employee_name,
        department,
        salary,
        RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS rnk
    FROM employees
)
SELECT *
FROM ranked_employees
WHERE rnk <= 3;

Use when:

• ties should be preserved
• and top positions are rank-based, not row-count-based

FAQ

What is the difference between ROW_NUMBER, RANK, and DENSE_RANK in SQL?

ROW_NUMBER gives every row a unique sequence number, RANK gives tied rows the same rank but leaves gaps after ties, and DENSE_RANK gives tied rows the same rank without leaving gaps.

When should I use ROW_NUMBER in SQL?

Use ROW_NUMBER when you need exactly one row per ordered position, such as deduplicating records, picking the latest row per group, or assigning a unique sequence within each partition.

When should I use RANK instead of DENSE_RANK?

Use RANK when ties should share the same position and later ranks should reflect the skipped positions caused by those ties, such as traditional competition ranking.

Do ROW_NUMBER, RANK, and DENSE_RANK require ORDER BY?

Yes in practice. These functions depend on a defined ordering inside the window, and without a meaningful ORDER BY, the ranking result is not logically reliable.

Final thoughts

SQL ROW_NUMBER, RANK, and DENSE_RANK are powerful because they let you assign ordered positions to rows without collapsing the result set.

That makes them ideal for:

• leaderboards
• deduplication
• top-N per group queries
• latest-row-per-entity logic
• and rank-based analytics

The most important ideas to remember are:

• ROW_NUMBER gives every row a unique number
• RANK gives ties the same rank and leaves gaps
• DENSE_RANK gives ties the same rank and leaves no gaps
• PARTITION BY restarts ranking inside groups
• ORDER BY defines what the ranking actually means
• and tie-breakers are essential when you need stable results

If you understand those ideas clearly, ranking functions stop feeling confusing and start becoming some of the most useful SQL tools you can reach for in real analytical and backend query work.