ROW_NUMBER

Numbering rows with ROW_NUMBER

Module 8 of 9 SQLite in your browser

Lesson concept What you'll do

1 / 4

Number the rows in order, or pick the latest loan per member.

ROW_NUMBER() OVER (ORDER BY ...) numbers every row and keeps them all.

Next: Numbering per member

On your own New data, same idea

Show each member's latest loan: member_id and loan_date. Number each member's loans newest first, then keep number 1.

The database

11 rows

loan_id	book_id	member_id	loan_date	returned
1	1	102	2026-01-02	2026-01-22
2	3	101	2026-02-10	2026-02-17
3	10	106	2026-03-05	2026-03-21
4	8	101	2026-04-07	2026-04-28
5	6	102	2026-05-20	2026-06-03
6	4	104	2026-06-22	2026-06-29
7	4	106	2026-08-02	2026-08-20
8	7	104	2026-08-11	2026-09-01
9	2	104	2026-09-09	NULL
10	5	105	2026-09-15	NULL
11	3	106	2026-10-28	NULL

Your query Ctrl + Enter runs it

Hint: click the green edit button on the board, or type it yourself

Query output Ready to run

Run your query to see the result table.

Press the green Run button or Ctrl+Enter.

Study notes

How ROW_NUMBER, OVER works

ROW_NUMBER() OVER (ORDER BY year) numbers every row 1, 2, 3 in year order.

query

SELECT title, year, 
  ROW_NUMBER() OVER (ORDER BY year) AS position
FROM books;

Every row stays: GROUP BY would squash each genre into one row instead.

What is a window function?

A function that works across rows but keeps every row: ROW_NUMBER() OVER (ORDER BY year). COUNT and GROUP BY squash rows into one; window functions add a column instead.
What goes in OVER?

ORDER BY says the order to count in. PARTITION BY member_id starts again for each member, like separate counts per group.
Why can't WHERE use the number?

WHERE runs before window functions are worked out. Put the numbering in a WITH step, then filter: WHERE n = 1.
What is it used for?

"The latest loan per member", "the first order per customer", removing duplicates (keep row 1), and numbering pages of results.
How is this like Excel?

Like a running count: =COUNTIF($B$2:B2, B2) filled down numbers each name's rows 1, 2, 3.

Other databases

The lessons run on SQLite. Most of SQL is the same everywhere; these are the places where this lesson's SQL differs.

Window functions (ROW_NUMBER, RANK, SUM OVER)
Database	How	Note
SQLite (these lessons)	`Yes`	Since SQLite 3.25 (2018).
PostgreSQL	`Yes`	Since PostgreSQL 8.4.
MySQL / MariaDB	`Yes`	Since MySQL 8.0. Not in 5.7.
SQL Server	`Yes`	ROW_NUMBER and RANK since 2005; running totals (SUM OVER ORDER BY) since 2012.
Oracle	`Yes`	Since Oracle 8i; Oracle calls them analytic functions.
Sybase ASE	`No`	Not in ASE. Number rows with a self-join or a temporary table with an IDENTITY column.

Keeping number 1 per group
Database	How	Note
SQLite (these lessons)	`WITH ... WHERE n = 1`	Or a subquery in FROM.
PostgreSQL	`DISTINCT ON (member_id) ... ORDER BY member_id, loan_date DESC`	A PostgreSQL shortcut for "first row per group".
MySQL / MariaDB	`WITH ... WHERE n = 1`
SQL Server	`WITH ... WHERE n = 1`
Oracle	`WITH ... WHERE n = 1`	Or a subquery in FROM.
Sybase ASE	`Not available`

Know someone fighting with a spreadsheet? Send them this page.