PostgreSQL DISTINCT ON selects the first row from each set sharing the specified expressions under the query’s supported ordering behavior. It is convenient for a latest-per-group task, but first is not meaningful without a deliberate ORDER BY. Ties and nulls can still make the selected record ambiguous.
A reliable query defines the group and the winning row separately. This guide explains ordering, filters, alternatives, and tests so compact SQL returns an evidence-backed choice rather than whichever row happens to appear first.
Define the group key from the business task
Identify which expressions make records belong to the same group. One row per customer, per device, or per customer-and-category are different requirements. Include every necessary grouping dimension.
Review tenant context and identifier scope. Grouping by a locally unique customer ID without its tenant can combine unrelated records if the schema permits that. Authorization and grouping should agree on object identity.
Use fixtures that distinguish groups clearly. A dataset where every record has a unique key cannot demonstrate whether DISTINCT ON selects the intended per-group result.
Understand what first means
The selected row is the first in each group under the supported ordering. Without an appropriate ORDER BY, PostgreSQL documents that the choice is unpredictable. A query returning the expected row once does not establish a stable contract.
Do not describe the operation as automatically finding the maximum timestamp. That outcome follows only when the ordering makes the intended latest row first.
Keep the requirement in a test with multiple candidates. Verify the chosen row’s identity and fields, not only that one result exists for each group.
Match the leftmost ordering expressions
DISTINCT ON expressions must match the leftmost ORDER BY expressions under the documented rules. Additional ordering expressions establish precedence inside each group.
For an illustrative latest-reading task, group by device and order by device, reading time descending, and a deliberate reading identity tie breaker. Each part has a different role.
If the final display needs a different global order, use an appropriate outer query over the selected results. Do not remove required ordering solely to make the displayed list look convenient.
Add a stable tie breaker
Two rows can share a timestamp or score. If only that value orders candidates, the chosen row may not be uniquely determined. Add a stable field that expresses the intended precedence.
A higher numeric ID is not universally a later business event. Use it as a tie breaker only when the rule is appropriate and documented. Event time and ingestion order can differ.
Test exact ties and repeated executions under representative conditions. A deterministic complete ordering is stronger than assuming physical storage order will remain unchanged after maintenance or another plan.
Decide how nulls participate
Null group expressions and null ordering values need an intentional policy. A missing timestamp can be treated differently from a valid latest measurement. Review PostgreSQL’s ordering and grouping behavior for the chosen expression.
Specify null placement where it affects winner selection. An automatic default can put an unknown time ahead of a known time under an ordering the author expected to mean latest.
Test groups containing only null times and mixed known and unknown values. Define whether the endpoint returns an unknown candidate, omits the group, or reports a separate incomplete state.
Place eligibility filters before selection
Decide which candidates are eligible before selecting one. Latest approved event is not the same as latest event followed by a check that it was approved. Those two query shapes can produce different groups and results.
Apply authorization and required status predicates at the correct layer. A denied candidate should not be selected and then leak through its associated fields or cause an authorized earlier candidate to disappear unexpectedly.
Use explicit tests for this distinction. Include a newer ineligible row and an older eligible row so the acceptance case reflects the actual task.
Compare alternatives when requirements differ
A window function can support ranking several rows per group or exposing rank information. Aggregation can identify a maximum value but does not automatically return all other columns from the same winning row.
Choose the query shape from the required result. Do not join a maximum timestamp back to the table and assume it returns exactly one row when ties are possible.
DISTINCT ON is a PostgreSQL-specific feature. If portability is a requirement, evaluate an appropriate alternative rather than presenting the syntax as universally accepted SQL.
Measure the actual query and index relationship
An index aligned with predicates and ordering can help, but performance depends on selectivity, data size, and the chosen plan. A compact statement is not automatically a cheap one.
Inspect a representative plan and measured resource use through an approved workflow. Include a large number of groups and uneven group sizes where those occur in production.
Do not add broad included payload merely to cover every possible result field. Evaluate read benefit against index storage and write maintenance. Correct selection and efficient execution are separate acceptance checks.
Validate the final result contract
Test empty input, one candidate, several candidates, ties, nulls, ineligible rows, and denied tenant records. Verify selected identities and any outer display ordering.
Keep query examples and diagnostics nonsecret. A latest-event report can contain private account state even when it returns only one row per group. Share controlled fixtures or minimized evidence.
For a latest-approved device reading, filter eligible authorized readings first, group by the complete device identity, and order with a stable tie breaker. That makes DISTINCT ON a clear selection policy rather than a shortcut around ambiguity.
Keep selection order and presentation order distinct
The ordering that chooses a winner per group can differ from the ordering the user wants for the selected winners. Preserve the required inner selection order, then apply the intended outer presentation order through a clear query boundary.
Test both layers independently. A list looking correctly sorted is not proof that it selected the right record inside each group, and a correct per-group choice is not proof that pagination uses a stable final order.
Frequently asked questions
Does DISTINCT ON automatically return the newest row?
No. The ordering must put the intended newest candidate first.
Can a maximum timestamp join return ties?
Yes. A maximum value does not inherently identify one unique record.
Where are ordering restrictions documented?
Read the PostgreSQL SELECT reference for DISTINCT ON semantics.
For a complementary workflow, read PostgreSQL Covering Indexes: Verify the Heap Work.
