Does an ORDER BY inside a derived table, CTE or view fix the outer query's row order?
answer
- Ask what a subquery produces: a table
- Tables carry no row order
- Only the last clause of the statement counts
- One exception: paired with a row limit
- Even then, repeat it on the outside
basics
~20 sNo. A subquery, CTE or view yields a table, and a table has no order, so only the statement's outermost ORDER BY constrains the result. An inner ORDER BY is at best ignored and some engines reject it outright.
solid answer
~50 sOrdering is a property of a *result*, not of a table. A derived table, a CTE and a view all produce tables that feed further processing, so an ORDER BY written inside one carries no guarantee: the outer query may join, filter or re-read those rows in any sequence, and the arrangement you asked for downstream can simply evaporate. Standard SQL does not even allow ORDER BY in that position except where it is paired with a row-limiting clause, and engines differ — some accept and ignore it, some accept and happen to honour it, and some reject it. Where it *is* meaningful is exactly that limiting case: in `(SELECT ... ORDER BY score DESC FETCH FIRST 10 ROWS ONLY)` the ORDER BY decides *which* ten rows survive, but still not the order they come out in. If the final result must be ordered, put an ORDER BY on the outermost query.
code
sql · 8 lines-- Misleading: the inner ORDER BY guarantees nothing downstream
SELECT t.customer_id, t.total
FROM (SELECT customer_id, total FROM orders ORDER BY total DESC) AS t;
-- Correct: order the query whose rows the caller receives
SELECT t.customer_id, t.total
FROM (SELECT customer_id, total FROM orders) AS t
ORDER BY t.total DESC;go deeper
Remember that the ORDER BY belongs on the outermost query. If a subquery or CTE has one and the final statement does not, the result order is not guaranteed.
Explain why: a subquery, CTE or view produces a table, and tables are unordered. Note the one meaningful case, where an inner ORDER BY paired with a row limit decides which rows survive.
Catch this in review, especially in views and stored queries where the inner clause implies a guarantee to every future consumer, and explain why a query that happens to work today is relying on one plan's behaviour.
Set the convention that ordering is part of a query's output contract, not a property baked into shared views or reusable query fragments, so the layer that returns rows to a client is the layer that pins the order.
## Tables have no order; results do Everything a query composes from — a base table, a view, a CTE, a derived table in FROM — is a table, and a table is a collection of rows with no defined sequence. Only the final result of a statement can carry an order, and only because ORDER BY put it there. That single fact answers the question: an ORDER BY buried inside a subquery is ordering something that is about to be consumed as an unordered table. ## What actually happens to the inner ORDER BY Engines take three different positions: - **Reject it.** Some products refuse ORDER BY in a subquery, view or derived table unless it is accompanied by a row-limiting clause, because in the standard the clause has no meaning there. - **Accept and ignore it.** The clause parses, contributes nothing, and may be optimised away entirely. - **Accept and often honour it.** Rows happen to emerge in that order because of how the plan is built — until a join method, a parallel read or a rewrite changes, at which point the order silently disappears. The third case is the dangerous one, because it trains people to believe the construct works. ## Where an inner ORDER BY is genuinely meaningful Pair it with a row-limiting clause and it stops being decorative, because now it selects membership: ```sql SELECT t.* FROM ( SELECT employee_id, salary FROM employees ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY ) AS t ORDER BY t.salary DESC; ``` The inner ORDER BY answers "which ten rows?" — without it, "first ten" is arbitrary. The outer ORDER BY answers "in what sequence do those ten come back?" Both are needed; neither substitutes for the other. Dropping the outer clause is a real bug that often goes unnoticed because ten rows frequently come out in the inner order by accident. ## Views and stored queries The same reasoning applies, with an extra sting: a view definition is written once and used by many queries. An ORDER BY in the view body suggests a guarantee it cannot make — every consumer that filters or joins the view may see a different arrangement, and each of them still has to write its own ORDER BY. Treat a view as a named table and leave ordering to the query that reads it. ## Set operations Branches of a UNION or similar operator are likewise unordered inputs. An ORDER BY belongs at the end of the whole statement, where it applies to the combined result, not inside a branch. ## The habit to build Write the ORDER BY on the query whose rows the caller actually receives. Inside a subquery, include ORDER BY only when a row-limiting clause makes it decide membership — and even then, repeat the ordering on the outer query if the caller cares how those rows are sequenced. ## Why people write it anyway Usually because it worked once. A small table read one way, or a plan that happened to feed rows through in sorted order, produced the expected output, and the construct entered the codebase as folklore. It is the same class of mistake as relying on the arrangement of rows tied on a sort key: an incidental behaviour of one plan mistaken for a language guarantee. Both are fixed the same way — by asking for what you need at the level where it is defined. ## Checklist - Outermost query orders the result. Always. - Inner ORDER BY without a row limit: delete it, or expect it to be ignored or rejected. - Inner ORDER BY with a row limit: keep it, and add the outer ORDER BY too. - Views and CTEs are tables — do not encode an order in their definition.
- Should a view definition contain an ORDER BY?No. A view is a named table, and every query reading it may filter, join or aggregate the rows in any sequence, so the ordering cannot be guaranteed to reach the caller. Worse, it implies a promise the view cannot keep. Leave ORDER BY to the queries that select from the view, where it applies to an actual result.
- Why does a row-limiting clause change the status of an inner ORDER BY?Because it turns the ordering into a membership decision. "The first ten rows" is meaningless without a defined sequence, so the ORDER BY determines which ten rows exist at all. That effect survives into the outer query, since the row set is now different. The output sequence, however, still needs an ORDER BY on the outermost query.
saying these in an interview costs you the question
- Believes a subquery's ORDER BY propagates to the result
- Puts ORDER BY in a view to fix output order
- Says the outer ORDER BY is redundant after an inner one
- Treats one engine honouring it as a guarantee
- Thinks a CTE preserves the order it was written with