ROW_NUMBER vs RANK vs DENSE_RANK

Editorial

Introduction

Now that you've learned about ROW_NUMBER, RANK, and DENSE_RANK individually, let's put them side by side to understand when to use each one. Choosing the right ranking function can make the difference between correct and incorrect results.

In this lesson, you will learn:

A direct comparison of all three functions
How each handles tied values differently
Decision criteria for choosing the right function
Real-world scenarios and best practices

Side-by-Side Comparison

Let's start with a comprehensive comparison table:

Feature	ROW_NUMBER	RANK	DENSE_RANK
Ties	Different numbers	Same number	Same number
Gaps	Never	After ties	Never
Sequence	Always 1,2,3,4,...	May skip: 1,1,3,4,...	Always consecutive: 1,1,2,3,...
Max Value	Row count	Row count	Distinct value count
Uniqueness	Always unique	May repeat	May repeat

Visualization

Side-by-side comparison of ROW_NUMBER, RANK, and DENSE_RANK applied to the same dataset with ties, showing different numbering behaviors

ROW_NUMBER vs RANK vs DENSE_RANK -- Complete Comparison

Visual Demonstration

Let's see all three functions on the same data:

-- Compare all three ranking functions
SELECT 
    order_id,
    price,
    ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num,
    RANK() OVER (ORDER BY price DESC) AS rank_val,
    DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank_val
FROM olist_order_items_dataset
LIMIT 25;

Example with Values: 100, 100, 90, 90, 80, 70

Price	ROW_NUMBER	RANK	DENSE_RANK
100	1	1	1
100	2	1	1
90	3	3	2
90	4	3	2
80	5	5	3
70	6	6	4

Observations:

ROW_NUMBER: 1-6, all unique, never repeats
RANK: 1,1,3,3,5,6 – ties share rank, then skips
DENSE_RANK: 1,1,2,2,3,4 – ties share rank, no skipping

Tie Handling Deep Dive

Understanding how ties are handled is crucial for choosing the right function.

ROW_NUMBER with Ties

ROW_NUMBER always assigns different numbers. When values tie, the order is non-deterministic (arbitrary):

-- ROW_NUMBER with ties - order is arbitrary
SELECT 
    product_id,
    price,
    ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num
FROM olist_order_items_dataset
WHERE price BETWEEN 99 AND 101
LIMIT 15;

RANK with Ties

RANK gives ties the same number, then skips to account for how many rows share that rank:

-- RANK with ties - same rank, then skip
SELECT 
    product_id,
    price,
    RANK() OVER (ORDER BY price DESC) AS rank_val
FROM olist_order_items_dataset
WHERE price BETWEEN 99 AND 101
LIMIT 15;

DENSE_RANK with Ties

DENSE_RANK gives ties the same number, then continues with the next consecutive number:

-- DENSE_RANK with ties - same rank, no skip
SELECT 
    product_id,
    price,
    DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank_val
FROM olist_order_items_dataset
WHERE price BETWEEN 99 AND 101
LIMIT 15;

Decision Guide

Use this decision tree to choose the right function:

Do you need unique numbers for every row?

YES → Use ROW_NUMBER
NO → Continue...

Should tied values get the same rank?

YES → Continue to next question
NO → Use ROW_NUMBER (but be aware of arbitrary tie handling)

Should the rank after ties reflect the "position among all rows"?

YES (gaps matter) → Use RANK
NO (just need consecutive tiers) → Use DENSE_RANK

Quick Reference

If You Need...	Use
Unique ID for each row	ROW_NUMBER
Pagination (rows 1-10, 11-20)	ROW_NUMBER
First/last record per group	ROW_NUMBER
Competition-style ranking (ties share, gaps after)	RANK
"How many rows are ranked higher?"	RANK
Tier/level assignment (consecutive numbers)	DENSE_RANK
"How many distinct values are ranked higher?"	DENSE_RANK
Top-N distinct values	DENSE_RANK

Practical Scenarios

Scenario 1: Selecting Top 3 Items

Different functions give different results for "top 3":

-- Top 3 with ROW_NUMBER (exactly 3 rows)
SELECT product_id, price, row_num
FROM (
    SELECT 
        product_id,
        price,
        ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num
    FROM olist_order_items_dataset
) r
WHERE row_num <= 3;

-- Top 3 with RANK (may include ties for 3rd)
SELECT product_id, price, rank_val
FROM (
    SELECT 
        product_id,
        price,
        RANK() OVER (ORDER BY price DESC) AS rank_val
    FROM olist_order_items_dataset
) r
WHERE rank_val <= 3
LIMIT 15;

-- Top 3 with DENSE_RANK (top 3 price tiers)
SELECT product_id, price, dense_rank_val
FROM (
    SELECT 
        product_id,
        price,
        DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank_val
    FROM olist_order_items_dataset
) r
WHERE dense_rank_val <= 3
LIMIT 15;

Scenario 2: Employee Salary Ranking

Imagine ranking employees by salary:

Employee	Salary	Context	Best Function
Need unique badge numbers	$80K	Arbitrary order OK	ROW_NUMBER
Competition ranking	$80K	"You're 5th out of 10"	RANK
Salary tier assignment	$80K	"Tier 3 salary"	DENSE_RANK

Within Partitions

All three functions work identically with PARTITION BY - they just restart ranking for each partition:

-- Compare all three with PARTITION BY
SELECT 
    seller_id,
    price,
    ROW_NUMBER() OVER (PARTITION BY seller_id ORDER BY price DESC) AS row_num,
    RANK() OVER (PARTITION BY seller_id ORDER BY price DESC) AS rank_val,
    DENSE_RANK() OVER (PARTITION BY seller_id ORDER BY price DESC) AS dense_rank_val
FROM olist_order_items_dataset
LIMIT 25;

Each seller's items are ranked independently, with numbering restarting for each partition.

Common Mistakes

Mistake 1: Using ROW_NUMBER When Ties Matter

Problem: You want the top 3 products by sales, but ROW_NUMBER arbitrarily excludes one of two tied products.

Solution: Use RANK or DENSE_RANK to include all ties.

Mistake 2: Using RANK for Pagination

Problem: Using WHERE rank <= 10 might return more than 10 rows if there are ties.

Solution: Use ROW_NUMBER for exact row counts in pagination.

Mistake 3: Expecting DENSE_RANK to Match Row Count

Problem: Assuming DENSE_RANK's maximum value equals the number of rows.

Solution: Remember that DENSE_RANK's max equals the number of distinct values.

Practice

Practice comparing all three functions with these exercises.

Exercise 1: Full Comparison

Compare all three functions on payment values:

-- Compare on payment data
SELECT 
    order_id,
    payment_value,
    ROW_NUMBER() OVER (ORDER BY payment_value DESC) AS row_num,
    RANK() OVER (ORDER BY payment_value DESC) AS rank_val,
    DENSE_RANK() OVER (ORDER BY payment_value DESC) AS dense_rank_val
FROM olist_order_payments_dataset
LIMIT 25;

Exercise 2: Top N Analysis

Experiment with different functions for top 5 selection:

-- Try changing the function to see different results
SELECT *
FROM (
    SELECT 
        seller_id,
        item_count,
        DENSE_RANK() OVER (ORDER BY item_count DESC) AS seller_rank
    FROM (
        SELECT seller_id, COUNT(*) AS item_count
        FROM olist_order_items_dataset
        GROUP BY seller_id
    ) counts
) ranked
WHERE seller_rank <= 5;

Exercise 3: Partitioned Comparison

Compare all three within partitions:

-- Compare within each order
SELECT 
    order_id,
    product_id,
    price,
    ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY price DESC) AS row_num,
    RANK() OVER (PARTITION BY order_id ORDER BY price DESC) AS rank_val,
    DENSE_RANK() OVER (PARTITION BY order_id ORDER BY price DESC) AS dense_rank
FROM olist_order_items_dataset
WHERE order_id IN (
    SELECT order_id 
    FROM olist_order_items_dataset 
    GROUP BY order_id 
    HAVING COUNT(*) >= 3
)
LIMIT 25;

Key Takeaways

Here's a summary of when to use each function:

ROW_NUMBER

Need unique sequential numbers
Pagination and slicing
Picking one record per group
Arbitrary selection when ties exist

RANK

Competition-style ranking
Need to know position among all rows
Include all ties in Top-N
Gaps after ties are acceptable/desired

DENSE_RANK

Tier/level assignment
Count distinct ranking positions
Top-N distinct values
Consecutive ranks without gaps

Final Decision Matrix

Requirement	ROW_NUMBER	RANK	DENSE_RANK
Unique numbers	✅	❌	❌
Same rank for ties	❌	✅	✅
Gaps after ties	❌	✅	❌
Exact row limit	✅	❌	❌
Fair tie handling	❌	✅	✅