MySQL ROW_NUMBER Function

MySQL doesn’t support ROWNUM() function, but it since version 8.0, MySQL introduced ROW_NUMBER() function as an equivalent to return the number of the current row within its partition during data retrieval.

Here is the generic syntax:

ROW_NUMBER() OVER (<partition_definition> <order_definition>)

Rows numbers range from 1 to the number of rows in the partition.

Let’s demonstrate via an example.

We have this table sales:

item	customer_name
book	Alex
pen	Bob
book	Alex
book	Jim
shoes	Jim
pen	Bob
backpack	Bob
book	Bob
wallet	Alex
wallet	Alex
book	Bob
backpack	Jim

1. ORDER BY

Since there’s no id column, we can use ROW_NUMBER() to assign each row of a table with a sequential value:

SELECT
	ROW_NUMBER() OVER (ORDER BY item) id,
	item,
	customer_name
FROM
	sales
ORDER BY
	item;

And we have the result numbered:

id	item	customer_name
1	backpack	Bob
2	backpack	Jim
3	book	Alex
4	book	Alex
5	book	Jim
6	book	Bob
7	book	Bob
8	pen	Bob
9	pen	Bob
10	shoes	Jim
11	wallet	Alex
12	wallet	Alex

2. PARTITION BY

If you want to divine the table sales into partitions by the column item, the number will reset for each partition:

SELECT
	ROW_NUMBER() OVER (PARTITION BY item) id,
	item,
	customer_name
FROM
	sales
ORDER BY
	item;

The result is:

id	item	customer_name
1	backpack	Bob
2	backpack	Jim
1	book	Alex
2	book	Alex
3	book	Jim
4	book	Bob
5	book	Bob
1	pen	Bob
2	pen	Bob
1	shoes	Jim
1	wallet	Alex
2	wallet	Alex

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.