Latest row per group in PostgreSQL: make row_number ordering deterministic
Select one whole event per account with an explicit tie-breaker and a deliberate policy for missing timestamps.
On this page
The short answer
Rank each group with row_number over PARTITION BY its identifier and ORDER BY the event timestamp plus a stable unique tie-breaker. Then filter rn=1 in an outer query. Use NULLS LAST if missing timestamps should lose to known timestamps. The window ordering selects the winner within a group; a separate outer ORDER BY controls the order in which selected rows are displayed.
Define what latest means
Specify the event timestamp used for selection and whether missing values are eligible. Ingestion time, business event time and a numeric identifier can represent different notions of recency. A higher identifier is a convenient deterministic tie-breaker in this example, not proof that an event happened later. Agree on that rule before writing the query, especially when events can arrive out of order.
Preserve the whole winning row
An aggregate such as max(event_time) finds a timestamp but does not by itself select the other columns from its corresponding row. Joining that maximum back to the table can return several rows when timestamps tie. Window ranking instead assigns a position to each candidate while retaining its payload and identifier. You still need an explicit policy if the business wants all tied latest events rather than just one.
Partition and sort independently
PARTITION BY account_id creates a separate ranking for each account. The window ORDER BY event_time DESC NULLS LAST, id DESC puts known recent timestamps first and breaks equal timestamps using the identifier. The identifier must distinguish candidate rows for that ordering to be deterministic. If the source does not provide such a key, choose another stable unique tie-breaker rather than relying on physical row storage order.
Follow the small query example
The constructed account 7 has events 10 and 11 with the same timestamp, so id DESC selects 11. Account 8 has only event 12, so it selects 12 even though its timestamp is missing. NULLS LAST does not remove missing values; it places them behind known values within each group. These are expected consequences of the query, not output from a query executed against this project.
WITH events (id, account_id, event_time, payload) AS (
VALUES
(10, 7, TIMESTAMP '2026-09-30 10:00:00', 'first'),
(11, 7, TIMESTAMP '2026-09-30 10:00:00', 'second'),
(12, 8, NULL::timestamp, 'unknown time')
), ranked AS (
SELECT events.*,
row_number() OVER (
PARTITION BY account_id
ORDER BY event_time DESC NULLS LAST, id DESC
) AS rn
FROM events
)
SELECT id, account_id, event_time, payload
FROM ranked
WHERE rn = 1
ORDER BY account_id;
Filter in the correct query layer
A window result is calculated after the input rows have been selected by that query layer. The CTE provides a layer where rn exists as a column, and the outer query filters it. Also distinguish filtering candidates before ranking from filtering selected winners afterward. For example, latest successful event and latest event that happens to be successful answer different questions and can produce different accounts in the output.
Set a policy for missing group identifiers
If account_id can be null, those rows form a partition together rather than automatically becoming separate accounts. Decide whether to exclude them before ranking or treat them as an explicitly defined group. Similarly, NULLS LAST permits an all-null timestamp partition to produce a winner. If unknown timestamps must never be selected, exclude them from the candidate input instead of expecting the ordering clause to remove them.
Check output ordering and concurrent changes
The outer ORDER BY account_id gives a display order; it does not change which event won. Without an outer ordering clause, applications should not assume rows arrive in a stable presentation order. New events inserted after the query reads its snapshot can change a later query result. Deterministic tie-breaking makes the selection well-defined for the rows considered, but does not freeze a changing dataset across separate queries.
Inspect performance on the real workload
Evaluate the query plan with realistic group sizes and selected columns before choosing an index. An index involving the partition and ordering columns may help some workloads, but it is not a universal promise that sorting disappears or that every payload is covered. Keep the correct selection semantics first. If choosing another PostgreSQL technique such as DISTINCT ON, preserve the same null and tie rules when comparing results.
Things to check
- Define the timestamp and null policy.
- Use a stable unique tie-breaker.
- Filter window results in an outer layer.
- Keep candidate filters separate from winner filters.
- Use outer ORDER BY for presentation.
Where this applies
The query selects one winner per partition under the stated ordering. It does not return all ties, infer event time from identifiers, exclude all-null groups automatically or guarantee an index plan. Example rows are hypothetical.