ROW_NUMBER()
Assign unique sequential numbers — and solve top-N-per-group
Advanced 12 minROW_NUMBERrankingwindow
Unique Row Numbers
ROW_NUMBER() assigns a unique sequential integer to each row in its partition, starting at 1. It never produces ties — even rows with identical values get different numbers. Here's the key pattern:
Top-N per group — Number rows with PARTITION BY, then filter the results.
WHERE rn = 1 keeps only the top item per group. WHERE rn <= 3 keeps the top three items per group for ranked displays.Wrap in a CTE — The standard flow: a CTE assigns row numbers using ROW_NUMBER(), then the outer query filters by rn. This solves "most expensive product per category" and "latest order per customer" elegantly.
Deduplication — Number duplicate rows within each group and keep only
rn = 1. This is a clean and efficient way to deduplicate data without using DELETE statements or creating temporary staging tables.Arbitrary tiebreaker — When two rows have identical ORDER BY values, which one gets rn=1 is unpredictable. Add more columns to your ORDER BY clause to ensure deterministic, reproducible numbering.
Knowledge Canvas
How ROW_NUMBER Works
Assign unique sequential numbers
- Assigns 1, 2, 3... to rows within each partition
- No ties — every row gets a unique number
- Tie-breaking is non-deterministic without sufficient ORDER BY
- Most common use: top-N-per-group pattern
Top-N Per Group
The killer pattern for ROW_NUMBER
- Assign ROW_NUMBER within each group (PARTITION BY)
- Filter to rn = 1 for "latest" or "best" per group
- Wrap in CTE or subquery for the WHERE filter
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY price DESC
) AS rn
FROM products
)
SELECT * FROM ranked WHERE rn = 1;ROW_NUMBER Pitfalls
Where it goes wrong
- Tied rows get arbitrary numbering without tiebreaker
- Add a unique column to ORDER BY for deterministic results
- Can't filter ROW_NUMBER in same query — use CTE
- PARTITION BY is optional — omit for global numbering
Syntax
Syntax Template
1ROW_NUMBER() OVER (
2 PARTITION BY group_col
3 ORDER BY sort_col DESC
4) AS rn
ROW_NUMBER()Always unique — 1, 2, 3... (no ties)PARTITION BYRestart numbering for each groupORDER BYDetermines which row gets number 1 Sample Data
products
10 rows
| name | category | price |
|---|---|---|
| Wireless Mouse | Electronics | 24.99 |
| Office Desk | Furniture | 189 |
| LED Desk Lamp | Furniture | 45.5 |
| USB-C Cable | Electronics | 12.99 |
| Bluetooth Speaker | Electronics | 79 |
| Office Chair | Furniture | 95 |
| Coffee Maker | Appliances | 89.99 |
| Notebook Set | Stationery | 8.5 |
| Wireless Headphones | Electronics | 159 |
| Standing Desk | Furniture | 425 |
orders
12 rows
| id | customer_id | order_date | total |
|---|---|---|---|
| 5001 | C1001 | 2024-01-15 | 59.98 |
| 5002 | C1002 | 2024-02-08 | 449 |
| 5003 | C1001 | 2024-02-20 | 12.5 |
| 5004 | C1003 | 2024-03-05 | 89.99 |
| 5005 | C1006 | 2024-03-15 | 245 |
| 5006 | C1005 | 2024-03-22 | 159 |
| 5007 | C1004 | 2024-04-02 | 45.5 |
| 5008 | C1009 | 2024-04-12 | 79 |
| 5009 | C1012 | 2024-04-22 | 425 |
| 5010 | C1007 | 2024-05-05 | 18.99 |
| 5011 | C1008 | 2024-05-18 | 134 |
| 5012 | C1010 | 2024-06-08 | 65.5 |
Worked Example
Find the most expensive product in each category.
SQL
1WITH ranked AS (
2 SELECT
3 name,
4 category,
5 price,
6 ROW_NUMBER() OVER (
7 PARTITION BY category
8 ORDER BY price DESC
9 ) AS rn
10 FROM products
11)
12SELECT
13 category,
14 name,
15 price
16FROM ranked
17WHERE rn = 1
18ORDER BY
19 price DESC;
ROW_NUMBER assigns 1 to the most expensive product within each category. The outer WHERE rn = 1 keeps only the top product per category. This is the classic "top 1 per group" pattern.
Output
3 rows
| category | name | price |
|---|---|---|
| Furniture | Standing Desk | 425 |
| Electronics | Wireless Headphones | 159 |
| Appliances | Coffee Maker | 89.99 |
Key Concepts
1ROW_NUMBER never ties — even identical values get unique numbers
2Top-N per group: CTE with ROW_NUMBER, then WHERE rn <= N
3Add more ORDER BY columns for deterministic tiebreaking
4Also works for deduplication: keep WHERE rn = 1
Pro Tip
ROW_NUMBER enables top-N-per-group queries — one of the most asked SQL interview questions and one of the most useful analytical patterns.
When to Use
Most expensive product per category, latest order per customer, deduplication, pagination, sampling N rows per group.
For each customer, find their 2 most recent orders. Show customer_id, order_date, total, and the row number.
Your Query