SQL RANK vs DENSE_RANK vs ROW_NUMBER Example

If you have ever needed to rank rows in SQL — finding the top performer in each department, assigning position numbers to sales results, or identifying the Nth highest value — you have encountered three functions that look almost identical but behave very differently:

• ROW_NUMBER()
• RANK()
• DENSE_RANK()

All three assign numbers to rows based on an ordering you define. But how they handle duplicate values is completely different and getting that wrong produces silently incorrect results that are very difficult to debug.

In this guide, we will break down all three functions with a real dataset, clear side-by-side comparisons, and practical examples so you always know which one to reach for.

What Are Ranking Window Functions?

All three are window functions — they operate on a set of rows related to the current row (called a window) and return a value for each row without collapsing the result like GROUP BY does.

Basic Syntax (Same for All Three)

sql

FUNCTION_NAME() OVER (
    PARTITION BY partition_column
    ORDER BY order_column
)

• PARTITION BY — Optional. Restarts ranking for each group
• ORDER BY — Required. Defines what order to rank by

The only difference between the three functions is what number they assign when two or more rows have the same ORDER BY value — the tie-breaking behavior.

Setting Up the Real Dataset

sql

CREATE TABLE sales_performance (
    employee_id   INT,
    name          VARCHAR(50),
    department    VARCHAR(50),
    region        VARCHAR(50),
    quarterly_sales DECIMAL(12, 2)
);

INSERT INTO sales_performance VALUES
(1,  'Alice',   'Electronics', 'North', 95000),
(2,  'Bob',     'Electronics', 'South', 82000),
(3,  'Charlie', 'Electronics', 'North', 95000),
(4,  'Diana',   'Electronics', 'East',  78000),
(5,  'Eve',     'Furniture',   'North', 67000),
(6,  'Frank',   'Furniture',   'South', 71000),
(7,  'Grace',   'Furniture',   'East',  71000),
(8,  'Henry',   'Furniture',   'West',  59000),
(9,  'Iris',    'Clothing',    'North', 88000),
(10, 'Jack',    'Clothing',    'South', 75000),
(11, 'Karen',   'Clothing',    'East',  88000),
(12, 'Leo',     'Clothing',    'West',  62000);

Key observation: Several employees have identical sales figures.

• Alice and Charlie both have 95,000 (Electronics)
• Frank and Grace both have 71,000 (Furniture)
• Iris and Karen both have 88,000 (Clothing)

These ties are exactly what reveal the difference between the three functions.

ROW_NUMBER() — Always Unique, No Ties

ROW_NUMBER() assigns a completely unique sequential number to every row — regardless of whether values are the same. No two rows ever get the same number.

When rows tie on the ORDER BY value, ROW_NUMBER() breaks the tie arbitrarily — the database engine decides which tied row gets which number, and the result can vary between executions unless you add a tiebreaker column to ORDER BY.

sql

SELECT
    name,
    department,
    quarterly_sales,
    ROW_NUMBER() OVER (ORDER BY quarterly_sales DESC) AS row_num
FROM sales_performance
ORDER BY quarterly_sales DESC;

Result:

name	department	quarterly_sales	row_num
Alice	Electronics	95000	1
Charlie	Electronics	95000	2
Iris	Clothing	88000	3
Karen	Clothing	88000	4
Bob	Electronics	82000	5
Jack	Clothing	75000	6
Diana	Electronics	78000	7
Frank	Furniture	71000	8
Grace	Furniture	71000	9
Eve	Furniture	67000	10
Leo	Clothing	62000	11
Henry	Furniture	59000	12

Alice and Charlie both have 95,000 but they receive row numbers 1 and 2 respectively. Iris and Karen both have 88,000 — they receive 3 and 4. Every row gets a unique number regardless of ties.

When to Use ROW_NUMBER()

• When you need exactly one row per entity — like the most recent record per customer or the latest version of a document
• When you want to paginate results — get rows 11 through 20 of a sorted list
• When you need to deduplicate — assign 1 to one row per group, filter for 1
• When ties genuinely should not produce the same number

RANK() — Ties Get Same Number, Gaps After Ties

RANK() assigns the same number to tied rows but then skips numbers equal to the count of tied rows before continuing the sequence.

If two rows tie at position 1, both get rank 1 and the next rank is 3, not 2. The number 2 is skipped. This creates gaps in the ranking sequence.

sql

SELECT
    name,
    department,
    quarterly_sales,
    RANK() OVER (ORDER BY quarterly_sales DESC) AS rnk
FROM sales_performance
ORDER BY quarterly_sales DESC;

Result:

name	department	quarterly_sales	rnk
Alice	Electronics	95000	1
Charlie	Electronics	95000	1
Iris	Clothing	88000	3
Karen	Clothing	88000	3
Bob	Electronics	82000	5
Diana	Electronics	78000	6
Jack	Clothing	75000	7
Frank	Furniture	71000	8
Grace	Furniture	71000	8
Eve	Furniture	67000	10
Leo	Clothing	62000	11
Henry	Furniture	59000	12

Alice and Charlie both get rank 1. The next rank is 3 (not 2) because position 2 was occupied by the second tied row. Iris and Karen both get rank 3. The next rank is 5 (not 4). Frank and Grace both get rank 8. The next rank is 10 (not 9).

Notice the gaps: 1, 1, 3, 3, 5, 6, 7, 8, 8, 10, 11, 12. Ranks 2, 4, and 9 are completely missing.

When to Use RANK()

• When you want ties to be acknowledged with the same position and gaps feel natural
• In competition-style rankings — if two athletes tie for gold, there is no silver medal (rank 3 is next)
• When you want to find rows at a specific rank position while correctly handling ties
• Leaderboard displays where tied positions should show the same rank number

DENSE_RANK() — Ties Get Same Number, No Gaps

DENSE_RANK() also assigns the same number to tied rows but unlike RANK(), it does NOT skip numbers. The sequence is always continuous — no gaps, no skipped positions.

sql

SELECT
    name,
    department,
    quarterly_sales,
    DENSE_RANK() OVER (ORDER BY quarterly_sales DESC) AS dense_rnk
FROM sales_performance
ORDER BY quarterly_sales DESC;

Result:

name	department	quarterly_sales	dense_rnk
Alice	Electronics	95000	1
Charlie	Electronics	95000	1
Iris	Clothing	88000	2
Karen	Clothing	88000	2
Bob	Electronics	82000	3
Diana	Electronics	78000	4
Jack	Clothing	75000	5
Frank	Furniture	71000	6
Grace	Furniture	71000	6
Eve	Furniture	67000	7
Leo	Clothing	62000	8
Henry	Furniture	59000	9

Alice and Charlie both get dense rank 1. Iris and Karen both get dense rank 2 (not 3 as RANK() gave them). The sequence is perfectly continuous: 1, 1, 2, 2, 3, 4, 5, 6, 6, 7, 8, 9 — no gaps at all.

When to Use DENSE_RANK()

• When you need continuous ranking with no gaps — “how many distinct performance levels are above this one?”
• Finding the Nth highest value — WHERE dense_rnk = 3 reliably returns the third-highest distinct value
• Salary banding — how many distinct salary levels exist, what band is each employee in
• Any scenario where gaps in ranking would be confusing or cause logic errors

All Three Side by Side — The Critical Comparison

This is the most important table in this guide — seeing all three functions on the same data makes the differences unmistakable.

sql

SELECT
    name,
    department,
    quarterly_sales,
    ROW_NUMBER() OVER (ORDER BY quarterly_sales DESC) AS row_num,
    RANK()       OVER (ORDER BY quarterly_sales DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY quarterly_sales DESC) AS dense_rnk
FROM sales_performance
ORDER BY quarterly_sales DESC;

Result:

name	department	quarterly_sales	row_num	rnk	dense_rnk
Alice	Electronics	95000	1	1	1
Charlie	Electronics	95000	2	1	1
Iris	Clothing	88000	3	3	2
Karen	Clothing	88000	4	3	2
Bob	Electronics	82000	5	5	3
Diana	Electronics	78000	6	6	4
Jack	Clothing	75000	7	7	5
Frank	Furniture	71000	8	8	6
Grace	Furniture	71000	9	8	6
Eve	Furniture	67000	10	10	7
Leo	Clothing	62000	11	11	8
Henry	Furniture	59000	12	12	9

Reading the differences at Alice and Charlie (tied at 95,000):

• ROW_NUMBER: 1 and 2 — arbitrarily different
• RANK: both 1 — next is 3 (skips 2)
• DENSE_RANK: both 1 — next is 2 (no skip)

Reading the differences at Iris and Karen (tied at 88,000):

• ROW_NUMBER: 3 and 4 — arbitrarily different
• RANK: both 3 — next is 5 (skips 4)
• DENSE_RANK: both 2 — next is 3 (no skip)

Using PARTITION BY — Ranking Within Groups

Add PARTITION BY to restart the ranking independently for each group — ranking employees within their department rather than across all departments.

sql

SELECT
    name,
    department,
    quarterly_sales,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY quarterly_sales DESC) AS row_num,
    RANK()       OVER (PARTITION BY department ORDER BY quarterly_sales DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY quarterly_sales DESC) AS dense_rnk
FROM sales_performance
ORDER BY department, quarterly_sales DESC;

Result:

name	department	quarterly_sales	row_num	rnk	dense_rnk
Iris	Clothing	88000	1	1	1
Karen	Clothing	88000	2	1	1
Jack	Clothing	75000	3	3	2
Leo	Clothing	62000	4	4	3
Alice	Electronics	95000	1	1	1
Charlie	Electronics	95000	2	1	1
Bob	Electronics	82000	3	3	2
Diana	Electronics	78000	4	4	3
Frank	Furniture	71000	1	1	1
Grace	Furniture	71000	2	1	1
Eve	Furniture	67000	3	3	2
Henry	Furniture	59000	4	4	3

Each department restarts from 1. Iris and Karen share rank 1 within Clothing. Alice and Charlie share rank 1 within Electronics. Frank and Grace share rank 1 within Furniture.

Practical Example 1: Get Top Performer Per Department

Use ROW_NUMBER() with PARTITION BY to get exactly one top performer per department — breaking ties arbitrarily.

sql

WITH ranked AS (
    SELECT
        name,
        department,
        quarterly_sales,
        ROW_NUMBER() OVER (
            PARTITION BY department
            ORDER BY quarterly_sales DESC
        ) AS row_num
    FROM sales_performance
)
SELECT name, department, quarterly_sales
FROM ranked
WHERE row_num = 1;

Result:

name	department	quarterly_sales
Iris	Clothing	88000
Alice	Electronics	95000
Frank	Furniture	71000

Only one row per department — even when there are ties. ROW_NUMBER() arbitrarily picks one tied employee.

If you want ALL tied top performers — use RANK() or DENSE_RANK() instead:

sql

WITH ranked AS (
    SELECT
        name,
        department,
        quarterly_sales,
        DENSE_RANK() OVER (
            PARTITION BY department
            ORDER BY quarterly_sales DESC
        ) AS dense_rnk
    FROM sales_performance
)
SELECT name, department, quarterly_sales
FROM ranked
WHERE dense_rnk = 1;

Result:

name	department	quarterly_sales
Iris	Clothing	88000
Karen	Clothing	88000
Alice	Electronics	95000
Charlie	Electronics	95000
Frank	Furniture	71000
Grace	Furniture	71000

All tied top performers appear — the correct result when ties should be equally recognized.

Practical Example 2: Find the Nth Highest Sales Value

DENSE_RANK() is the correct function for finding the Nth highest distinct value — because it has no gaps that could make your WHERE clause return nothing.

sql

-- Find the 3rd highest distinct sales figure
WITH ranked AS (
    SELECT
        name,
        department,
        quarterly_sales,
        DENSE_RANK() OVER (ORDER BY quarterly_sales DESC) AS dense_rnk
    FROM sales_performance
)
SELECT name, department, quarterly_sales
FROM ranked
WHERE dense_rnk = 3;

Result:

name	department	quarterly_sales
Bob	Electronics	82000

The 3rd highest distinct value is 82,000 (after 95,000 and 88,000). DENSE_RANK() reliably returns this — RANK() would return the same result here, but on different data the gaps in RANK() can cause WHERE rnk = N to return nothing.

Practical Example 3: Deduplication With ROW_NUMBER()

One of the most common real-world uses of ROW_NUMBER() — removing duplicate records by keeping only one row per entity.

sql

-- Simulate duplicate records
WITH duplicated_data AS (
    SELECT name, department, quarterly_sales FROM sales_performance
    UNION ALL
    SELECT name, department, quarterly_sales FROM sales_performance
    WHERE name IN ('Alice', 'Bob')  -- Alice and Bob appear twice
),
deduplicated AS (
    SELECT
        name,
        department,
        quarterly_sales,
        ROW_NUMBER() OVER (
            PARTITION BY name
            ORDER BY quarterly_sales DESC
        ) AS row_num
    FROM duplicated_data
)
SELECT name, department, quarterly_sales
FROM deduplicated
WHERE row_num = 1;

ROW_NUMBER() assigns 1 to the first occurrence of each name. Filtering for row_num = 1 keeps exactly one row per employee — removing the duplicates.

Practical Example 4: Percentile Ranking With NTILE

As a bonus — NTILE() divides rows into N equal buckets. Combined with RANK/DENSE_RANK it enables percentile-based segmentation.

sql

SELECT
    name,
    department,
    quarterly_sales,
    DENSE_RANK() OVER (ORDER BY quarterly_sales DESC) AS sales_rank,
    NTILE(4) OVER (ORDER BY quarterly_sales DESC) AS quartile,
    CASE NTILE(4) OVER (ORDER BY quarterly_sales DESC)
        WHEN 1 THEN 'Top 25%'
        WHEN 2 THEN 'Upper Middle'
        WHEN 3 THEN 'Lower Middle'
        WHEN 4 THEN 'Bottom 25%'
    END AS performance_band
FROM sales_performance
ORDER BY quarterly_sales DESC;

Full Comparison Table

Feature	ROW_NUMBER()	RANK()	DENSE_RANK()
Ties get same number	No — always unique	Yes	Yes
Creates gaps after ties	N/A	Yes — skips numbers	No — continuous
Sequence always 1,2,3…	Yes	No (gaps on ties)	Yes (no gaps)
Finds Nth highest value	Unreliable with ties	May skip N	Best choice
Gets exactly one row per group	Yes	No (ties return multiple)	No (ties return multiple)
Deduplication	Yes — ideal	No	No
Competition-style ranking	No	Yes	No gaps
Pagination	Yes	No	No

Database Compatibility

Database	ROW_NUMBER()	RANK()	DENSE_RANK()
SQL Server 2005+	Yes	Yes	Yes
PostgreSQL 8.4+	Yes	Yes	Yes
MySQL 8.0+	Yes	Yes	Yes
Oracle 8i+	Yes	Yes	Yes
SQLite 3.25+	Yes	Yes	Yes
BigQuery	Yes	Yes	Yes
Snowflake	Yes	Yes	Yes

All three functions are universally supported in modern SQL databases.

Real-World Use Cases

Sales Leaderboards — RANK() or DENSE_RANK() with PARTITION BY territory or product line. Tied performers at the same position is meaningful — if two reps hit the same quota, both deserve first place recognition.

Employee Performance Reviews — DENSE_RANK() within department to identify top, mid, and lower performers without gaps confusing the classification.

Deduplication in ETL Pipelines — ROW_NUMBER() with PARTITION BY entity ID and ORDER BY timestamp descending — filter for row_num = 1 to keep the most recent record per entity.

Pagination in APIs — ROW_NUMBER() over a sorted result, then WHERE row_num BETWEEN (page-1)page_size+1 AND pagepage_size to return a specific page of results.

Finding Nth Highest Salary — DENSE_RANK() with WHERE dense_rnk = N — the most reliable approach that handles duplicates correctly without gaps.

Customer Segmentation — NTILE() or DENSE_RANK() over purchase frequency or lifetime value to assign customers to tiers (Platinum, Gold, Silver, Bronze).

Common Mistakes to Avoid

• Using RANK() to find Nth highest value — If ties exist at ranks before N, RANK() skips numbers and WHERE rnk = N may return nothing. Always use DENSE_RANK() for Nth value queries
• Using ROW_NUMBER() when ties should be equal — If your business logic says two tied employees should share first place, ROW_NUMBER() unfairly gives one person rank 1 and the other rank 2. Use RANK() or DENSE_RANK() instead
• Not adding a tiebreaker to ROW_NUMBER() — When using ROW_NUMBER() for deduplication or pagination, the ordering for tied rows is arbitrary. Add a secondary column (like ID or timestamp) to ORDER BY to make the result deterministic: ORDER BY sales DESC, employee_id ASC
• Forgetting PARTITION BY when ranking within groups — Without PARTITION BY, all rows are ranked together globally. A department-level leaderboard needs PARTITION BY department to restart rankings per department
• Confusing the absence of gaps in DENSE_RANK with RANK — RANK() has gaps. DENSE_RANK() does not. Mixing them up in a query that filters by rank number produces wrong results without any error message

Quick Reference Cheat Sheet

Task	Use This
Unique number per row, no ties	ROW_NUMBER()
Same rank for ties, gaps after	RANK()
Same rank for ties, no gaps	DENSE_RANK()
Get exactly 1 row per group	ROW_NUMBER() + WHERE row_num = 1
Get ALL tied top performers	DENSE_RANK() + WHERE dense_rnk = 1
Find Nth highest value	DENSE_RANK() + WHERE dense_rnk = N
Deduplicate records	ROW_NUMBER() + WHERE row_num = 1
Paginate results	ROW_NUMBER() + WHERE BETWEEN
Department-level ranking	Any + PARTITION BY department

ROW_NUMBER(), RANK(), and DENSE_RANK() are three of the most used window functions in SQL and the difference between them comes down entirely to one question: what should happen when rows tie?

Here is the simplest memory aid:

• ROW_NUMBER — No ties ever. Every row gets a unique number. Use for deduplication and pagination.
• RANK — Ties get same number. Gaps appear after ties. Use for competition-style rankings.
• DENSE_RANK — Ties get same number. No gaps. Use for Nth value queries and continuous ranking.

When you are unsure, DENSE_RANK() is usually the safest choice — it handles ties correctly and never produces gaps that break downstream filtering logic.

FAQs