SQL ROW_NUMBER
Assigns a unique, ever-increasing number to each row — never repeats, even when values tie.
What & Why
ROW_NUMBER numbers rows 1, 2, 3, and so on inside the window, even when ordered values tie. It gives every row one stable position for pagination, deduplication, and outer-query filtering.
See How It Works
BUSINESS QUESTION
Number campaigns from highest to lowest spend.
| id | name | channel | spend | start_date | end_date | status | target_segment |
|---|---|---|---|---|---|---|---|
| 1 | Spring Launch | google_ads | 55000.00 | 2024-01-15 | 2024-03-31 | active | smb |
| 2 | Retention Webinar | 45000.00 | 2024-02-10 | 2024-04-15 | active | enterprise | |
| 3 | Finance Retargeting | 50000.00 | 2024-03-12 | 2024-05-31 | active | enterprise | |
| 4 | Enterprise Search | google_ads | 60000.00 | 2024-04-01 | 2024-06-30 | active | enterprise |
Watch ROW_NUMBER assign one unique campaign positionStep 1 of 4
ROW_NUMBER() OVER (ORDER BY spend DESC, id)
| campaign_id | name | channel | spend | row_number |
|---|---|---|---|---|
| 4 | Enterprise Search | google_ads | 60,000.00 | 1 |
| 1 | Spring Launch | google_ads | 55,000.00 | 2 |
| 3 | Finance Retargeting | 50,000.00 | 3 | |
| 2 | Retention Webinar | 45,000.00 | 4 |
Enterprise Search receives the first position as the highest spend.
EXAMPLE QUERY
SELECT
id AS campaign_id,
name,
channel,
spend,
ROW_NUMBER() OVER (ORDER BY spend DESC, id) AS row_number
FROM marketing.campaigns
ORDER BY row_number;This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario