skip to content

In PostgreSQL, how does SELECT DISTINCT ON pick the latest price per stock symbol, and what must its ORDER BY look like?

level: middleimportance: must knowfreq 68%

answer

  1. first row of each group
  2. the sort decides the survivor
  3. ON keys lead the ORDER BY
  4. unique tiebreaker after the timestamp
  5. DESC puts NULLs first

basics

~20 s

DISTINCT ON (symbol) keeps the first row of each symbol group in sort order, so ORDER BY must begin with symbol and then traded_at DESC to choose that row; add a unique tiebreaker so ties are deterministic.

solid answer

~40 s

`SELECT DISTINCT ON (symbol) symbol, price, traded_at FROM ticks ORDER BY symbol, traded_at DESC` sorts the rows and keeps the first row of each run of equal `symbol` values; it is a PostgreSQL extension, not standard SQL. The `DISTINCT ON` expressions must match the **leftmost** `ORDER BY` items: putting `traded_at DESC` first fails with `SELECT DISTINCT ON expressions must match initial ORDER BY expressions` (SQLSTATE `42P10`). The items after that prefix choose the survivor, and without them the kept row is unpredictable. If two ticks share a timestamp, add a unique column such as `id DESC`; if `traded_at` can be NULL, write `NULLS LAST`, because `DESC` sorts NULLs first. To order the output by something else, such as price, wrap the query in a subquery and sort outside it.

code

sql · 4 lines
sql
SELECT DISTINCT ON (symbol) symbol, price, traded_at
FROM ticks
WHERE traded_at IS NOT NULL
ORDER BY symbol, traded_at DESC, id DESC;

go deeper

for a junior

Recall that DISTINCT ON keeps the first row of each group after sorting, and that ORDER BY must start with the DISTINCT ON columns.

for a middle

Explain the leftmost ORDER BY rule and its 42P10 error, why later ORDER BY items pick the survivor, and why a unique tiebreaker and NULLS LAST matter.

for a senior

Show you have debugged nondeterministic latest-row reports: tie ordering that changes with the plan, NULL timestamps winning under DESC, and wrapping the query to re-sort output.

for a principal

Weigh DISTINCT ON's brevity against portability: a codebase that may move engines or needs N rows per group is better served by the window-function form.

## What DISTINCT ON does `SELECT DISTINCT ON (expr, ...)` is a **PostgreSQL extension** to `SELECT`. It groups the rows that arrive after `ORDER BY` sorting into sets whose `DISTINCT ON` expressions are equal, and it keeps **only the first row of each set**. Every other column of that row comes along, which is why it is the shortest way to say "the whole latest row per key". In a stock-ticker application with a `ticks` table holding one row per trade, the canonical query is: ```sql SELECT DISTINCT ON (symbol) symbol, price, traded_at FROM ticks ORDER BY symbol, traded_at DESC, id DESC; ``` Key facts: - The manual is explicit that the "first row" of each set is **unpredictable unless `ORDER BY`** forces the desired row to the top. - `DISTINCT ON` processing happens **after** the sort, so the sort is the whole mechanism for choosing the survivor. - `NULL` values of the key are grouped together, as with plain `DISTINCT`: all ticks with a NULL `symbol` form one group. - The `DISTINCT ON` expressions do not have to appear in the select list; the parser adds them as hidden (resjunk) target entries. ## The leading ORDER BY rule The parser enforces one rule: the `DISTINCT ON` expressions must match the **leftmost** `ORDER BY` items. Their order among themselves and their sort direction are free, but no other sort key may come before them. Breaking the rule raises `SELECT DISTINCT ON expressions must match initial ORDER BY expressions` with SQLSTATE `42P10` (`invalid_column_reference`). | Query shape | Result | |---|---| | `DISTINCT ON (symbol) ... ORDER BY symbol, traded_at DESC` | Valid: newest tick per symbol | | `DISTINCT ON (symbol) ... ORDER BY symbol DESC, traded_at DESC` | Valid: same rows, symbols in reverse order | | `DISTINCT ON (symbol) ... ORDER BY traded_at DESC` | Error 42P10: a non-key item leads | | `DISTINCT ON (symbol, venue) ... ORDER BY venue, symbol, traded_at DESC` | Valid: the keys may lead in any order | | `DISTINCT ON (symbol) ...` with no `ORDER BY` | Valid but the kept row is arbitrary | The rule exists because `DISTINCT ON` works on adjacent equal rows: the sort must bring each group together first, and only the keys that come after the prefix can rank rows inside a group. ## Choosing the survivor: tiebreakers and NULLs Two traps make a correct-looking query return the wrong row: 1. **Ties.** Two ticks for `ACME` with the same `traded_at` are equal for the sort, and PostgreSQL makes no promise about which comes first. Results can differ between runs or after a plan change. Append a unique column, such as an identity `id DESC`, so the order is total. 2. **NULLs.** In PostgreSQL NULLs sort as if larger than any value, so `NULLS FIRST` is the default for `DESC`. A tick with a NULL `traded_at` therefore wins its group. Write `ORDER BY symbol, traded_at DESC NULLS LAST`, or exclude NULLs in `WHERE`. ## Ordering the final result another way Because `ORDER BY` is consumed by the rule, you cannot directly sort the latest prices by price. Wrap the query: ```sql SELECT * FROM ( SELECT DISTINCT ON (symbol) symbol, price, traded_at FROM ticks ORDER BY symbol, traded_at DESC, id DESC ) latest ORDER BY price DESC; ``` The outer `ORDER BY` sorts only one row per symbol, so it is cheap. ## How the query executes Without a helpful index, the plan is a `Sort` on `(symbol, traded_at DESC, id DESC)` feeding a `Unique` node, which passes the first row of each run of equal symbols and discards the rest. Three consequences follow: - Window functions in the same `SELECT` are computed **before** `DISTINCT ON`, so they see every tick, not only the survivors. - `LIMIT` applies **after** `DISTINCT ON`, so `LIMIT 10` returns ten symbols, not ten ticks. - The sort covers the whole input, so a large table makes this an expensive query unless an index supplies the order; that is a separate tuning question. ## Where DISTINCT ON fits - **Plain `DISTINCT`** removes fully duplicate rows and is standard SQL; it cannot keep "the row with the highest timestamp". - **`GROUP BY symbol` with `max(traded_at)`** returns only the aggregate. Getting the price requires joining back on `(symbol, traded_at)`, which returns several rows when the maximum ties. - **`ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY traded_at DESC)`** is the portable top-N-per-group pattern (owned by the window-functions topic); it generalises to N rows per group, while `DISTINCT ON` returns exactly one. In an interview, the strong answer names the prefix rule, explains that the remaining `ORDER BY` items choose the survivor, and volunteers the tiebreaker and `NULLS LAST` without being asked.

  • Must the DISTINCT ON column appear in the select list?
    No. PostgreSQL adds `DISTINCT ON` expressions to the target list as hidden entries if they are not already there, so `SELECT DISTINCT ON (symbol) price, traded_at ... ORDER BY symbol, traded_at DESC` is valid. It is usually clearer to show the key anyway, since each output row otherwise loses the label of its group.
  • How do you list the latest prices sorted by price, highest first?
    Put the `DISTINCT ON` query in a subquery with its required `ORDER BY symbol, traded_at DESC, id DESC`, then apply `ORDER BY price DESC` in the outer query. The inner sort chooses the survivors; the outer sort only orders one row per symbol.
  • Why not use GROUP BY symbol with max(traded_at) instead?
    `GROUP BY` returns only the key and aggregates. To get the price you must join back to `ticks` on `(symbol, traded_at)`, which scans the data twice and returns duplicate rows when two ticks share the maximum timestamp. `DISTINCT ON` returns the whole row once per symbol in one pass.

  • DISTINCT ON keeps the most recent row by itself, without any ORDER BY.
  • ORDER BY traded_at DESC alone is fine after DISTINCT ON (symbol).
  • Timestamp ties are broken by insertion order or the primary key automatically.
  • DESC places NULL timestamps last, so a missing time can never win.
  • The DISTINCT ON column must also be listed in the SELECT list.
  • DISTINCT ON is standard SQL and behaves the same in every engine.