Module 6: Window functions · Lesson 6.2 · about 25 min
ROW_NUMBER, RANK and DENSE_RANK
Three ways to number rows in order, and they only differ when there are ties. See the difference row by row on six orders, then rank stores and staff on Northbay.
Module 6 · about 25 minutes · read first: 6.1. You’ll be able to number and rank rows with ROW_NUMBER, RANK and DENSE_RANK, choose the right one when there are ties, and rank inside groups.
In everyday terms: a race where two runners cross the line together in third. RANK says both are 3rd and nobody is 4th; DENSE_RANK says both are 3rd and the next is 4th; ROW_NUMBER makes the judge put one of them 3rd and the other 4th.
1. Why this matters
“Top 10 products”, “best store in each region”, “the customer’s first order”: they all start by putting rows in order and numbering them. The three ranking functions look alike, and the classic interview question is to explain how they differ. They only differ when two rows tie, and real data is full of ties.
2. Three ways to number rows
Ranking functions are window functions with an ORDER BY inside the OVER brackets. That ORDER BY says what “first” means; it does not sort the output.
- ROW_NUMBER(): 1, 2, 3, 4 … every row a new number, ties or not.
- RANK(): ties share a number, then the next number is skipped (1, 2, 3, 3, 5), like a race with two runners in third place.
- DENSE_RANK(): ties share a number, no gaps (1, 2, 3, 3, 4).
mini_orders has two orders of $45, so all three show up:
-- try
SELECT order_id, amount,
RANK() OVER (ORDER BY amount DESC) AS rnk,
ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,
DENSE_RANK() OVER (ORDER BY amount DESC) AS dense
FROM mini_orders
Press Show how it runs and step to OVER. The animation draws the first window column, rnk. What to watch: the window for each row grows from the top of the ordered list down to that row, and orders 101 and 105 (both $45) both get 3, so the $20 order gets 5. Move ROW_NUMBER() to the first column and run the animation again to see 3 and 4 instead.
Maths and principle
Principle: ranking counts how many rows come before you in an order. The three functions count slightly different things.
Equation: for row r ordered by x descending: RANK(r) = 1 + |{ s : s.x > r.x }| (rows strictly ahead). DENSE_RANK(r) = 1 + |{ distinct s.x : s.x > r.x }| (distinct values ahead). ROW_NUMBER(r) = position of r in one chosen total order, so it is a bijection from n rows onto {1, …, n}.
Analogy: an exam. RANK is how a school reports places: two people tie for 3rd, nobody is 4th. DENSE_RANK is “which grade band are you in”: the bands are numbered without gaps. ROW_NUMBER is the seat number when everyone lines up, and with a tie someone has to pick who stands first. It breaks down a little because a school has rules for that choice, and SQL does not unless you give it one.
Try it: values 90, 60, 45, 45, 20, 5. What is DENSE_RANK of the 5? (Distinct values ahead: 90, 60, 45, 20, so 5.)
3. Make ROW_NUMBER repeatable
When two rows tie, which gets row number 3 is up to the database. It may change after an update or on another server. Add a tie-breaker, a second ORDER BY column that is unique, so the answer is always the same:
-- try
SELECT order_id, amount,
ROW_NUMBER() OVER (ORDER BY amount DESC, order_id) AS row_num
FROM mini_orders
Now order 101 always comes before 105. Use the table’s key as the last tie-breaker by habit.
4. Rank inside groups with PARTITION BY
Add PARTITION BY and the numbering starts again at 1 in each group. Here staff at stores 1 and 2 are ranked by salary within their own store:
-- try
SELECT name, store_id, salary,
RANK() OVER (PARTITION BY store_id ORDER BY salary DESC) AS pay_rank
FROM employees
WHERE store_id IN (1, 2)
Press Show how it runs: at the OVER step, the two stores are separate coloured partitions, and the count restarts at 1 when the window moves into store 2. Both store managers come out as rank 1.
- ROW_NUMBER()
- Gives 1, 2, 3, ... with no repeats, even on ties.
- RANK()
- Ties share a number, and the next number is skipped (1, 2, 3, 3, 5).
- DENSE_RANK()
- Ties share a number, with no gap after (1, 2, 3, 3, 4).
- ORDER BY inside OVER
- Says what "first" means. It does not sort the output.
- Tie-breaker
- An extra ORDER BY column (such as an id) that makes ROW_NUMBER give the same answer every time.
- Physical meaning
- Each row gets a new number column; no rows are removed. RANK counts the rows strictly ahead of you; DENSE_RANK counts the different values ahead of you.
“ROW_NUMBER always gives the same numbers.” When two rows tie on the ORDER BY, the database may number them either way, and the order can change between runs. Add a tie-breaker such as ORDER BY amount DESC, order_id.
Watch it happen, one step at a time (any live box’s Show how it runs button animates the real query on Northbay):
| order_id | amount |
|---|---|
| 104 | 90 |
| 106 | 60 |
| 101 | 45 |
| 105 | 45 |
| 103 | 20 |
| 102 | 5 |
5. Worked example
Question: rank the Ontario stores (store_id 1 to 7) by completed orders in 2025.
-- try
WITH s AS (
SELECT store_id, COUNT(*) AS orders_2025
FROM orders
WHERE status = 'completed'
AND order_date BETWEEN '2025-01-01' AND '2025-12-31'
AND store_id <= 7
GROUP BY store_id
)
SELECT store_id, orders_2025,
RANK() OVER (ORDER BY orders_2025 DESC) AS rnk
FROM s
ORDER BY rnk, store_id
- The CTE gives one row per store: 7 rows.
- Store 4 (Ottawa) leads with 161, then store 2 with 158.
- Stores 5 and 7 both have 127, so both are 5th, and store 1 (121) is 7th: no store is 6th.
- With DENSE_RANK, store 1 would be 6th. With ROW_NUMBER and no tie-breaker, one of stores 5 and 7 would be 6th, and you could not say which.
Which is right? If a manager asks “how many stores beat store 1?”, RANK answers it: 6 stores, so 7th place.
6. Practice
-- exercise: Rank the Kitchen products (category_id = 6) from most to least expensive with DENSE_RANK, in a column called price_rank.
-- hint: DENSE_RANK() OVER (ORDER BY unit_price DESC).
SELECT product_name, unit_price
FROM products
WHERE category_id = 6
-- answer:
SELECT product_name, unit_price,
DENSE_RANK() OVER (ORDER BY unit_price DESC) AS price_rank
FROM products
WHERE category_id = 6
-- exercise: Number the orders of each customer in mini_orders from biggest to smallest (1 = biggest), breaking ties by order_id.
-- hint: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, order_id).
SELECT order_id, customer_id, amount,
ROW_NUMBER() OVER (ORDER BY amount DESC) AS n
FROM mini_orders
-- answer:
SELECT order_id, customer_id, amount,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, order_id) AS n
FROM mini_orders
-- exercise: Rank all head office and online staff (store_id IS NULL or store_id = 12) by salary, highest = 1.
-- hint: Filter in WHERE first, then RANK() OVER (ORDER BY salary DESC).
SELECT name, role, salary
FROM employees
-- answer:
SELECT name, role, salary,
RANK() OVER (ORDER BY salary DESC) AS pay_rank
FROM employees
WHERE store_id IS NULL OR store_id = 12
- All three ranking functions need ORDER BY inside OVER.
- ROW_NUMBER never repeats; RANK repeats on ties and leaves gaps; DENSE_RANK repeats without gaps.
- Add a tie-breaker column so ROW_NUMBER is repeatable.
- PARTITION BY restarts the numbering in each group.
New words: ROW_NUMBER, RANK, DENSE_RANK, tie-breaker.
Check yourself
Sources
- SQLite documentation, Window functions: built-in window functions https://www.sqlite.org/windowfunctions.html#builtins
- Microsoft Learn, RANK (Transact-SQL) https://learn.microsoft.com/en-us/sql/t-sql/functions/rank-transact-sql
- PostgreSQL documentation, Window functions https://www.postgresql.org/docs/current/functions-window.html
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Retail is a made-up company; its data is generated in your browser. Queries run on your device and nothing you type is sent anywhere.