ROW_NUMBER vs RANK vs DENSE_RANK

Technical Explanation

These three window functions assign rankings to rows within a partition. They differ in how they handle ties (rows with equal values).

The Difference

Function	Handles Ties	Example Output
ROW_NUMBER	Unique sequential	1, 2, 3, 4, 5
RANK	Same rank + gaps	1, 2, 2, 4, 5
DENSE_RANK	Same rank, no gaps	1, 2, 2, 3, 4

Visual Comparison

value:  100  90  90  80  70

ROW_NUMBER():  1    2    3    4    5   (all unique)
RANK():        1    2    2    4    5   (gap after tied values)
DENSE_RANK():  1    2    2    3    4   (no gaps)

---

Code Examples

Using the CatCafe dataset:

-- Table: orders
-- id, customer_id, cat_id, order_date, total_amount, status

-- Example data: Customers with total spending
WITH customer_spending AS (
    SELECT
        customer_id,
        SUM(total_amount) as total_spent
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT
    customer_id,
    total_spent,
    ROW_NUMBER() OVER (ORDER BY total_spent DESC) as row_num,
    RANK() OVER (ORDER BY total_spent DESC) as rank,
    DENSE_RANK() OVER (ORDER BY total_spent DESC) as dense_rank
FROM customer_spending
ORDER BY total_spent DESC;

-- Practical: Top 3 customers per city
WITH ranked_customers AS (
    SELECT
        c.name,
        c.city,
        c.total_spent,
        ROW_NUMBER() OVER (
            PARTITION BY c.city
            ORDER BY c.total_spent DESC
        ) as rank_in_city
    FROM customers c
)
SELECT *
FROM ranked_customers
WHERE rank_in_city <= 3
ORDER BY city, rank_in_city;

-- Finding "first" row per group (arbitrary selection)
-- Example: One order per customer (any order)
SELECT *
FROM (
    SELECT
        o.*,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC
        ) as seq
    FROM orders o
    WHERE status = 'completed'
) ranked
WHERE seq = 1;

-- DENSE_RANK for percentile grouping
SELECT
    customer_id,
    total_spent,
    DENSE_RANK() OVER (ORDER BY total_spent DESC) as spend_rank,
    CASE DENSE_RANK() OVER (ORDER BY total_spent DESC)
        WHEN 1 THEN 'Top Tier'
        WHEN 2 THEN 'High Value'
        WHEN 3 THEN 'Mid Tier'
        ELSE 'Standard'
    END as tier
FROM (
    SELECT customer_id, SUM(total_amount) as total_spent
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
) spending;

---

The Cat Analogy

You're ranking cats at a beauty contest:

ROW_NUMBER() — Strict lineup:

Position 1: Whiskers (you must pick someone)
Position 2: Mochi
Position 3: Luna
Position 4: Shadow

Only one cat per position. If two cats are equally pretty, one still gets position 1 and the other position 2.

RANK() — Official competition ranking:

1st place: Whiskers (100 points)
2nd place: Mochi (95 points) ← tie
2nd place: Luna (95 points)  ← same rank
4th place: Shadow (80 points)

Tied cats share rank. Next position skips to 4th.

DENSE_RANK() — Close competition ranking:

1st place: Whiskers
2nd place: Mochi ← tie
2nd place: Luna
3rd place: Shadow

Tied cats share rank. No gaps—everyone after gets the next sequential rank.

---

When to Use Each

Function	Use Case
ROW_NUMBER()	Selecting exactly N rows per group (no ties, predictable)
RANK()	Strict competition ranking (ties skip positions)
DENSE_RANK()	Tiered grouping (no gaps between tiers)

Example Decision Tree

Need exactly one row per group?
  → ROW_NUMBER() WHERE seq = 1

Need to rank by score with gaps?
  → RANK()

Need tier assignments (Gold/Silver/Bronze)?
  → DENSE_RANK()

---

Exercises

Exercise 1

Given values: 100, 90, 90, 80, 90, 70 What does each function return?

Exercise 2

Write a query to get the top 5 spending customers overall using ROW_NUMBER.

Exercise 3

Explain why this might return more than 5 rows:

SELECT *
FROM (
    SELECT *, RANK() OVER (ORDER BY total_spent DESC) as rank
    FROM customers
) ranked
WHERE rank <= 5;

Exercise 4

Write a query to assign "tiers" to customers based on their ranking:

• Top 10%: "Platinum"
• Top 11-30%: "Gold"
• Top 31-60%: "Silver"
• Rest: "Bronze"

Exercise 5

Write a query to find the second-highest spending customer per city.

---

Key Takeaways

• ROW_NUMBER: Always unique (1, 2, 3, 4)
• RANK: Ties get same rank, next rank skips (1, 2, 2, 4)
• DENSE_RANK: Ties get same rank, no gaps (1, 2, 2, 3)
• Use ROW_NUMBER when you need exactly N rows per group
• Use RANK for traditional competition-style ranking
• Use DENSE_RANK for tiered groupings without gaps
• All three require ORDER BY in the OVER() clause