TIL: Postgres Distinct On Clause

I stumbled upon a really handy feature in Postgres today: DISTINCT ON.

I needed to find the latest entry from a history table for each record in a main table. The history table tracks changes over time (like status updates or edits), so naturally, there are multiple entries per record.

My first instinct was to go with the usual ROW_NUMBER() OVER (PARTITION BY ...) approach. It works fine. You partition by the record ID, order by timestamp descending, and filter to the first row. However, I found a more concise way to do the same thing: using DISTINCT ON clauses.

With DISTINCT ON, you can tell Postgres to return only the first row of each group based on how you sort the data. For example, if you want the latest entry for each record_id, you can write:

SELECT DISTINCT ON (record_id) *
FROM history
ORDER BY record_id, updated_at DESC;

This gives you one row per record_id, and because we are ordering by updated_at DESC, it returns the most recent one.

Here’s the official Postgres docs on DISTINCT if you want to dive deeper.