ROW_NUMBER
- SQL course
- Advanced
- Lesson 14/81
- 4 min
Number each row.
ROW_NUMBER
ROW_NUMBER() gives each row a unique sequential number. Starting from 1.
SELECT name,
ROW_NUMBER() OVER (ORDER BY id) AS rn
FROM products;This query gives each row 1, 2, 3...
name, ROW_NUMBER() OVER (ORDER BY id) AS rn is selected from products. ROW_NUMBER() is a window function: it computes a value for each row over the window defined in OVER (…), without collapsing rows.
ROW_NUMBER — details
ROW_NUMBER gives different numbers even on ties. RANK and DENSE_RANK give the same number on ties.
That is why ROW_NUMBER is used for tasks like pagination or picking one row per group; when ties matter in a ranking, RANK or DENSE_RANK is preferred.
Practice ROW_NUMBER
Give each product a row number with ROW_NUMBER() OVER (ORDER BY id).
Starter code
-- row number
SELECT name,
ROW_NUMBER()Sample solution
SELECT name,
ROW_NUMBER() OVER (ORDER BY id) AS rn
FROM products;Expected output
name rn Kalem 1 Defter 2
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Add ROW_NUMBER
SELECT name,
____() OVER (ORDER BY id) AS rn
FROM products;SELECT name,
ROW_NUMBER() OVER (ORDER BY id) AS rn
FROM products;Explanation: ROW_NUMBER is the right answer.
2 What does ROW_NUMBER do on ties?
- Same number
- Different numbers Correct answer
- Skips
- An error
Explanation: ROW_NUMBER gives different numbers on ties.
3 ROW_NUMBER: match the terms
| Term | Meaning |
|---|---|
ROW_NUMBER |
row number |
unique |
unique |
starts at 1 |
sequential |
ties |
different |