ROW_NUMBER
Numbers the rows of a window partition in ORDER BY order, starting from 1.
MySQL 8.4
ROW_NUMBER()Return value
An integer (BIGINT UNSIGNED) from 1 to the number of rows in the partition
Examples
Row numbers by status
MySQL 8.4
SELECT member_name,
status,
ROW_NUMBER() OVER (
ORDER BY status
) AS row_num
FROM FamilyMembers;Numbering within each status
MySQL 8.4
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. Without ORDER BY the numbering order is undefined. 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.