ROW_NUMBER, RANK, DENSE_RANK: Ranking Rows

These three window functions assign a rank to each row within a partition. They differ only in how they handle ties.

Function	Ties get the same rank?	Gaps after ties?
ROW_NUMBER()	No - always unique	-
RANK()	Yes	Yes
DENSE_RANK()	Yes	No

Prior lesson

This lesson builds on Window Functions. The sample dataset is defined in the Introduction to SQL lesson.

Syntax

ROW_NUMBER()   OVER (PARTITION BY col ORDER BY col)
RANK()         OVER (PARTITION BY col ORDER BY col)
DENSE_RANK()   OVER (PARTITION BY col ORDER BY col)

ORDER BY inside OVER is required for all three.

Examples

Example 1: Compare all three on order amounts

SELECT
    order_id,
    customer_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;

Result:

order_id	customer_id	total_amount	row_num	rnk	dense_rnk
1	1	999.00	1	1	1
7	2	999.00	2	1	1
9	3	999.00	3	1	1
4	3	796.00	4	4	2
3	2	349.00	5	5	3
6	1	349.00	6	5	3
8	5	89.97	7	7	4
2	1	59.98	8	8	5
5	4	49.90	9	9	6
10	4	24.95	10	10	7

Orders 1, 7, 9 all have 999.00. RANK gives all three rank 1 then jumps to 4. DENSE_RANK gives them rank 1 then continues at 2.

Example 2: Top order per customer (ROW_NUMBER pattern)

ROW_NUMBER is ideal for "keep only the first/last N rows per group" - a very common pattern.

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

Result - the single highest-value order per customer:

order_id	customer_id	total_amount	order_date
1	1	999.00	2024-01-05
7	2	999.00	2024-03-15
9	3	999.00	2024-04-10
5	4	49.90	2024-02-10
8	5	89.97	2024-04-01

Common Mistake

ROW_NUMBER ties are broken arbitrarily

When two rows have the same ORDER BY value, ROW_NUMBER assigns different numbers - but which row gets 1 vs 2 is non-deterministic. Add a tiebreaker column (like order_id) to make the result stable.

ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_amount DESC, order_id ASC)

Practice

Write a query that ranks customers by their total spend (SUM(total_amount)) using DENSE_RANK. Return customer_id, total_spent, and spend_rank.

Show answer

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT
    customer_id,
    total_spent,
    DENSE_RANK() OVER (ORDER BY total_spent DESC) AS spend_rank
FROM customer_totals;

Expected result:

customer_id	total_spent	spend_rank
3	1795.00	1
1	1407.98	2
2	1348.00	3
5	89.97	4
4	74.85	5