AI Hints
Homeโ€บSQL Tutorialโ€บWindow Functions
SQL ยท Lesson 63 of 126

ROW_NUMBER

Numbering rows in order.

โฑ 2 min readโ–ถ Video at 1:30:35๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Helen wants to number her books, from most expensive to cheapest. One, two, three, and so on. Row number does exactly that.

ROW_NUMBER
From the video: ROW_NUMBER
  • 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