How to Get the Latest Row per Group in PostgreSQL with DISTINCT ON

When a PostgreSQL table stores several events for each account, you may need the newest event for each account. DISTINCT ON is PostgreSQL’s concise way to choose one row from each group of rows. It keeps the first row in each group, so the ORDER BY clause must put the row you want first.

Use DISTINCT ON (group_column) with an ORDER BY that starts with the same grouping column and sorts the timestamp from newest to oldest. Add a tie-breaker, such as a unique ID, to choose between rows that have the same timestamp.

Select the newest row in each group

For example, suppose an events table has an account_id, an occurred_at timestamp, an event_id, and a payload. To return each account’s newest event:

SELECT DISTINCT ON (account_id)
       account_id,
       event_id,
       occurred_at,
       payload
FROM events
ORDER BY account_id,
         occurred_at DESC NULLS LAST,
         event_id DESC;

DISTINCT ON (account_id) keeps one row for each account. PostgreSQL reads the rows in the order specified: first by account, then by timestamp from newest to oldest. It keeps the first row it encounters for each account and discards the rest. PostgreSQL documents this behavior and requires the DISTINCT ON expressions to match the leftmost expressions in ORDER BY (PostgreSQL’s SELECT documentation).

event_id breaks ties. If two events for the same account have the same timestamp, the query chooses the one with the larger ID. Choose a column that reflects your tie-breaking rule, ideally one with a unique value for every row. Without a tie-breaker, PostgreSQL can choose either row.

NULLS LAST puts rows with a missing timestamp after rows with actual timestamps. In PostgreSQL, descending order otherwise places nulls first by default. If every timestamp in a group is null, the query still chooses one row, using event_id to break the tie.

Keep the ordering rule intact

The columns or expressions in DISTINCT ON must come first in ORDER BY. For example, DISTINCT ON (account_id) works with ORDER BY account_id, occurred_at DESC. Starting with occurred_at breaks this rule. PostgreSQL also uses the sort order to choose which row to keep, so sorting only by account_id does not identify the newest event.

The query’s output follows the ORDER BY clause. The example sorts results by account and then timestamp, rather than listing every account’s newest event in one global newest-to-oldest order. To sort the selected rows globally, put the query inside a subquery and apply an outer ordering:

SELECT *
FROM (
    SELECT DISTINCT ON (account_id)
           account_id,
           event_id,
           occurred_at,
           payload
    FROM events
    ORDER BY account_id,
             occurred_at DESC NULLS LAST,
             event_id DESC
) AS latest_events
ORDER BY occurred_at DESC NULLS LAST, event_id DESC;

Add an index, then check the query plan

An index that starts with the grouping column and then lists the columns used to sort can help PostgreSQL find rows in the order the query needs. For this example, consider an index on account_id, occurred_at, and event_id. Set the sort directions and null placement to match the query.

An index does not guarantee that PostgreSQL reads only one row per account. Depending on the data and query plan, PostgreSQL can still scan many rows in each group before returning the selected rows. Check the actual plan with EXPLAIN ANALYZE on representative data before deciding whether the index or query is fast enough. The discussion of selecting the first row in each group describes this scanning concern and other query approaches.

Use another pattern when it fits better

DISTINCT ON is a PostgreSQL extension, so other SQL databases do not all support queries that use it. A window function such as row_number() ranks rows within each group, and you can keep the row with rank 1. This approach is part of standard SQL. A GROUP BY with max(timestamp) finds the newest timestamp in each group, but it does not return the other columns from that row. To get those columns, join the result back to the original table and choose how to handle rows with matching timestamps. PostgreSQL developers also use JOIN LATERAL to select a top row separately for each row in another table. These alternatives are discussed in examples of selecting the last row per ID.

Performance depends on the table’s size and shape, the available indexes, and how the query is written. Compare the alternatives with the data and query patterns you actually use rather than assuming one form is always faster.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com