DISTINCT ON

SQL construct

Keeps one row for each value of the expressions in parentheses, the first one in ORDER BY order.

PostgreSQL 17.9

SELECT DISTINCT ON (expression[, ...]) column[, ...] FROM table ORDER BY expression[, ...]

expression: Expressions by which rows count as duplicates
column: Columns of the result
table: Table to select rows from
Name
ORDER BY: Order of rows: starts with the same expressions as DISTINCT ON, and the next columns decide which row is kept

Return value

A set of rows, one for each unique value of expression

PostgreSQL 17.9

SELECT DISTINCT ON (status) status,
	member_name,
	birthday
FROM FamilyMembers
ORDER BY status,
	birthday;

PostgreSQL 17.9

SELECT DISTINCT ON (family_member) family_member,
	date,
	good,
	unit_price
FROM Payments
ORDER BY family_member,
	date DESC;

family_member	date	good	unit_price
1	2006-03-12	5	10
2	2005-10-23	14	66000
3	2006-01-12	10	100
4	2005-07-26	7	150
5	2005-12-22	15	250

It is a PostgreSQL extension: in MySQL the same task is solved with ROW_NUMBER() in a subquery.

Without ORDER BY the kept row is arbitrary. If ORDER BY starts with other expressions, the query fails with an error.