Skip to content

.tip

· one of the small things

DISTINCT ON is the latest-row-per-group query

You don't need a window function or a self join for "the most recent order for each customer".

SELECT DISTINCT ON (customer_id) *
FROM orders
ORDER BY customer_id, created_at DESC;

The ORDER BY has to start with the DISTINCT ON columns, and you get whichever row sorts first in each group. An index on (customer_id, created_at DESC) makes it fast.

postgresql