Where can ORDER BY appear in a UNION query, and what does it sort?

## The result is one result The mental model that makes all of this obvious: `A UNION B` is not "query A, then query B". It is a single query expression whose result is derived from both. `ORDER BY` is not part of either branch — it is applied to the query expression as a whole. Hence there is exactly one place it can go, at the end: ```sql SELECT name, hired_on FROM employees UNION ALL SELECT name, started_on FROM contractors ORDER BY hired_on DESC; ``` The sort applies to all rows from both branches, interleaved. ## What the sort can reference Since the sort operates on the combined result, it sees that result's columns and nothing else. Two consequences follow. **Names come from the first branch.** In the example above the second column is called `hired_on` because the first query named it so; `started_on` is not a visible name in the combined result and sorting by it fails. If you want a neutral name, alias the first branch: ```sql SELECT name, hired_on AS start_date FROM employees UNION ALL SELECT name, started_on FROM contractors ORDER BY start_date DESC; ``` **Ordinal positions always work.** `ORDER BY 2 DESC` sorts by the second output column whatever it is named, which is why you see positional sorts far more often after set operations than in ordinary queries. It is a legitimate use, though an alias is more readable and survives a change to the select list. What you generally cannot do is sort by an arbitrary expression over the input tables — `ORDER BY UPPER(e.name)` referring to a branch's table alias has no meaning once the branches have been combined. Compute the expression as an output column in **both** branches and sort by that column instead. ## Per-branch ORDER BY Writing `ORDER BY` inside a branch of a set operation is not part of the standard grammar: the sort belongs to the query expression, not to a query specification inside it. Several engines do accept a *parenthesized* branch carrying its own `ORDER BY` together with a row limit, which is how you express "the ten newest employees plus the five newest contractors": ```sql (SELECT name, hired_on FROM employees ORDER BY hired_on DESC FETCH FIRST 10 ROWS ONLY) UNION ALL (SELECT name, started_on FROM contractors ORDER BY started_on DESC FETCH FIRST 5 ROWS ONLY) ORDER BY 2 DESC; ``` Two things to understand about that shape. First, the per-branch `ORDER BY` is there to define *which* rows the limit keeps — it is doing selection, not presentation. Second, the ordering inside a branch is not guaranteed to survive into the combined result; only the trailing `ORDER BY` determines the order the client sees. If you drop the final `ORDER BY 2 DESC` above, the rows may come back in any order at all, and an engine that happens to emit them branch-by-branch today is under no obligation to keep doing so. Whether parenthesized branches with their own sorts and limits are accepted at all varies by engine, so check yours before relying on the syntax. ## LIMIT and FETCH FIRST The same rule governs row limiting. A `FETCH FIRST n ROWS ONLY` (or `LIMIT n`) written after the last branch applies to the combined, sorted result — it is the top *n* of everything, not *n* per branch. To get *n* per branch you need the parenthesized-branch form above, with its engine-dependence. And the usual warning applies with extra force here: a row limit without an `ORDER BY` is non-deterministic. Combined with a set operation, which makes no promise about which branch's rows appear first, "the first 10 rows of a UNION" without a sort is essentially an arbitrary sample. ## The mistakes this question is probing - Writing an `ORDER BY` after the first branch and expecting the statement to parse, or expecting it to order only that branch. - Expecting `UNION ALL` to emit the left branch's rows first without any sort. - Sorting by a column name that only exists in the second branch. - Assuming a trailing `LIMIT` applies per branch. All four dissolve once you hold the model: the set operator produces one result, and `ORDER BY` and the row limit belong to that result.