Why does SELECT DISTINCT city FROM users ORDER BY created_at raise an error?
answer
- Sorting happens after duplicates are removed
- The collapsed row has many candidate timestamps
- Sort keys must appear in the select list
- Group and aggregate to pick a representative
basics
~20 sWith SELECT DISTINCT, ORDER BY may only sort by expressions that appear in the select list. After deduplication one city row stands for many users with different created_at values, so that sort key no longer has a single well-defined value.
solid answer
~40 s`DISTINCT` collapses many source rows into one output row. Sorting happens after that collapse, so the only values still available to sort by are the ones in the select list. `created_at` was not selected and, worse, the deduplicated `'Berlin'` row corresponds to hundreds of users with hundreds of different timestamps — the standard requires each sort key to appear in the select list precisely because there is otherwise no answer to "which created_at?". The fix depends on intent. If you want one row per city ordered by its newest signup, aggregate and choose a representative: `SELECT city FROM users GROUP BY city ORDER BY MAX(created_at) DESC`. If you actually wanted city-and-timestamp pairs, select `created_at` too — but understand that this changes the deduplication grain and will return far more rows.
go deeper
Recall the rule as a rule: with DISTINCT, you may only sort by columns you selected. Recognise the error message when you meet it and know the two obvious repairs.
Explain why the restriction exists — deduplication happens before sorting, so a collapsed row has many candidate values for an unselected column and the engine has no basis for choosing one.
Diagnose from intent rather than syntax: decide whether the caller wanted one row per city or city-and-timestamp pairs, and show the grouping rewrite that makes the representative value an explicit choice.
Own the portability stance: queries whose ordering is not fully determined by the query text are a latent source of cross-engine and cross-version behaviour change, and should not pass review even where an engine tolerates them.
## The rule When `DISTINCT` is specified, every sort key in `ORDER BY` must be an expression that appears in the select list. You can reference it by repeating the expression, by its column alias, or by its ordinal position (`ORDER BY 1`). Referencing anything else — a column of the underlying table that you did not select, or an expression merely derivable from one you did — is not allowed. ```sql SELECT DISTINCT city FROM users ORDER BY created_at; -- rejected SELECT DISTINCT city FROM users ORDER BY city; -- fine SELECT DISTINCT city FROM users ORDER BY 1; -- fine, same thing ``` ## Why the restriction exists It is not arbitrary syntax policing; it is a consequence of the order in which the clauses take effect. Logically, rows come from `FROM` and `WHERE`, the select list is evaluated, `DISTINCT` removes duplicates, and only then does `ORDER BY` sort the surviving rows. By the time sorting runs, a single output row can be the merged representative of thousands of input rows. Suppose 400 users live in Berlin, each with a different `created_at`. The output row `('Berlin')` does not have *a* `created_at` — it has 400 candidates and no rule for choosing among them. The engine cannot silently pick one, because any choice would make the result of the query depend on physical storage or plan shape rather than on the query text. Rejecting the statement is the honest response. This is the same reasoning that forbids sorting a grouped result by an ungrouped, unaggregated column: once rows have been collapsed, the collapsed-away values are gone. ## What counts as "in the select list" The requirement is on the expression appearing in the select list, not on it being derivable from what you selected. `SELECT DISTINCT city FROM users ORDER BY UPPER(city)` is commonly rejected too, even though the sort key is a pure function of a selected column and would in fact be well defined. If you need that ordering, select the expression: ```sql SELECT DISTINCT city, UPPER(city) AS city_uc FROM users ORDER BY city_uc; ``` Note what this costs: `UPPER(city)` is now part of the deduplication key, which in this particular case is harmless because it is determined by `city`, but in general adding a column to make the sort legal also changes which rows are considered duplicates. ## Fixing it, by intent There are three honest repairs, and choosing between them means deciding what the query is for. **One row per city, ordered by something computed from its rows.** Group and aggregate. This makes the choice explicit — you are ordering by the maximum timestamp, not by "the" timestamp: ```sql SELECT city FROM users GROUP BY city ORDER BY MAX(created_at) DESC; ``` **City-and-timestamp pairs.** Select the column. Be clear that the result is no longer a list of cities: ```sql SELECT DISTINCT city, created_at FROM users ORDER BY created_at DESC; ``` With timestamps this typically returns nearly one row per user, since two users rarely share a timestamp to the microsecond. That is usually a sign this was not the intent. **Deduplicate first, sort outside.** Wrapping the deduplication in a derived table and sorting the outer query works only for keys that survive the collapse, so it solves alias and expression awkwardness, not the missing-column problem: ```sql SELECT city FROM (SELECT DISTINCT city FROM users) d ORDER BY city; ``` ## Portability and a related trap Engines differ in how strictly they enforce the rule; some reject the query outright while others have historically accepted forms whose ordering is then not well defined. Do not rely on the lax behaviour — a query whose sort key is ambiguous can legitimately return a different order on a different engine, a different version, or a different plan. The related trap is the opposite mistake: assuming `DISTINCT` implies an order. It does not. Deduplication may be implemented in ways that happen to produce sorted output, but nothing in the language guarantees it. If you need a particular order you must write `ORDER BY`, and — as this question shows — you must write one the deduplicated result can actually support. ## Mistakes to avoid Believing `ORDER BY` runs before `DISTINCT`; adding the missing column to the select list without noticing the grain change; assuming an engine's tolerance is the standard's rule; and treating deduplicated output as implicitly sorted.
- Does adding created_at to the select list fix it, and what does that cost?It makes the statement legal, but it changes the deduplication key: rows are now unique per (city, created_at) pair, so a list of a few hundred cities becomes nearly one row per user. It is a fix only if pairs were what you wanted.
- Is ORDER BY UPPER(city) allowed alongside SELECT DISTINCT city?Commonly not, even though the sort key is a function of a selected column. The rule is about the expression appearing in the select list, not about being derivable from it. Select `UPPER(city)` as an aliased column and order by that alias instead.
- Does SELECT DISTINCT guarantee any ordering of its own?No. Deduplication may be implemented in ways that happen to emit rows in a sorted or clustered order, but the language guarantees nothing. Relying on it produces results that change with the engine, the version, or the chosen plan. Write an explicit ORDER BY whenever order matters.
saying these in an interview costs you the question
- Thinks ORDER BY is evaluated before DISTINCT
- Says the sort column just needs an index
- Adds the column to SELECT without noticing the grain change
- Assumes DISTINCT already returns sorted rows
- Treats one engine's tolerance as the SQL rule