ROW_NUMBER
Numbers the rows of a window partition in ORDER BY order, starting from 1.
MySQL 8.1
ROW_NUMBER() OVER ([PARTITION BY ...] [ORDER BY ...])Parameters
- PARTITION BYoptional
- Columns that split rows into partitions, numbering restarts in each one
- ORDER BYoptional
- Order in which rows are numbered; without it rows are numbered in an arbitrary order
Return value
An integer (BIGINT UNSIGNED) from 1 to the number of rows in the partition
Examples
Row numbers by status
MySQL 8.1
SELECT member_name,
status,
ROW_NUMBER() OVER (
ORDER BY status
) AS row_num
FROM FamilyMembers;Numbering within each status
MySQL 8.1
SELECT member_name,
status,
birthday,
ROW_NUMBER() OVER (
PARTITION BY status
ORDER BY birthday
) AS num_in_status
FROM FamilyMembers;Details
Rows with equal ORDER BY values still get different numbers in an arbitrary order. A window function cannot be used in WHERE: to keep, for example, the first row of each partition, compute the number in a subquery and filter in the outer query.