What is DISTINCT ON in PostgreSQL?

DISTINCT ON is an extension of PostgreSQL for selecting unique rows based on specific columns, while allowing control over which row to pick from each group. Unlike the standard DISTINCT, which returns unique combinations of all selected columns, DISTINCT ON allows selecting the first row for each group based on specified columns, considering the sort order.

Example:

SELECT DISTINCT ON (user_id) user_id, order_date, amount
FROM orders
ORDER BY user_id, order_date DESC;

This query will return one record for each user_id — the one with the most recent order date (order_date DESC).

Important: DISTINCT ON requires an ORDER BY clause, where the first fields must match the fields in DISTINCT ON.