SQL ยท Lesson 63 of 126
ROW_NUMBER
Numbering rows in order.
Helen wants to number her books, from most expensive to cheapest. One, two, three, and so on. Row number does exactly that.

- Number the books
- 1, 2, 3 ...
- From dearest to cheapest
ROW_NUMBER
Row number, with empty brackets, then over. Inside over, order by price descending says which order to count in. Run it. The most expensive book is number one.
query.sqlsql
SELECT title,
price,
ROW_NUMBER() OVER (ORDER BY price DESC) AS position
FROM books
LIMIT 5;Output
โโโโโโโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโฌโโโโโโโโโโโ โ title โ price โ position โ โโโโโโโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโผโโโโโโโโโโโค โ Python for Curious Minds โ 34.99 โ 1 โ โ Data for Everyone โ 31.99 โ 2 โ โ SQL in a Weekend โ 29.99 โ 3 โ โ Spice Roads โ 27.99 โ 4 โ โ The Quiet Kitchen โ 24.99 โ 5 โ โโโโโโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโดโโโโโโโโโโโ
Numbering inside each group
Add partition by genre, and the counting starts again at one for every genre. Run it. Spice Roads is the most expensive cooking book, and The Glass Forest is the most expensive fantasy book.
query.sqlsql
SELECT genre,
title,
price,
ROW_NUMBER() OVER (PARTITION BY genre
ORDER BY price DESC) AS rank_in_genre
FROM books
WHERE genre IN ('Cooking', 'Fantasy');Output
โโโโโโโโโโโฌโโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโฌโโโโโโโโโโโโโโโโ โ genre โ title โ price โ rank_in_genre โ โโโโโโโโโโโผโโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโผโโโโโโโโโโโโโโโโค โ Cooking โ Spice Roads โ 27.99 โ 1 โ โ Cooking โ The Quiet Kitchen โ 24.99 โ 2 โ โ Cooking โ One Pan Dinners โ 21.49 โ 3 โ โ Fantasy โ The Glass Forest โ 18.49 โ 1 โ โ Fantasy โ Dragons of Ember Bay โ 17.99 โ 2 โ โ Fantasy โ Crown of Ash โ 16.99 โ 3 โ โโโโโโโโโโโดโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโดโโโโโโโโโโโโโโโโ
Summary
Row number counts rows in order, and partition by restarts the count for each group. Next, what happens with ties.
Key takeaways- ROW_NUMBER() OVER (ORDER BY ...)
- PARTITION BY restarts it
- 1, 2, 3 per group
Next up: RANK and DENSE_RANK โ handling ties