ROW_NUMBER

Numbers the rows of a window partition in ORDER BY order, starting from 1.

PostgreSQL 17.9

ROW_NUMBER()

Return value

An integer (bigint) from 1

PostgreSQL 17.9

SELECT member_name,
	status,
	ROW_NUMBER() OVER (
		ORDER BY status
	) AS row_num
FROM FamilyMembers;

PostgreSQL 17.9

SELECT member_name,
	status,
	birthday,
	ROW_NUMBER() OVER (
		PARTITION BY status
		ORDER BY birthday
	) AS num_in_status
FROM FamilyMembers;

member_name	status	birthday	num_in_status
Lela Quincey	daughter	1985-06-07	1
Annie Quincey	daughter	1988-04-10	2
Wednesday Addams	daughter	2005-01-13	3
Headley Quincey	father	1960-05-13	1
Ernest Forrest	father	1961-09-11	2
Flavia Quincey	mother	1963-02-16	1
Constance Forrest	mother	1968-09-06	2
Andie Quincey	son	1983-06-05	1

Rows with equal ORDER BY values still get different numbers in an arbitrary order. Without ORDER BY the numbering order is undefined.