Does ORDER BY inside OVER () also determine the order of the query's output rows?

## Two ORDER BYs, one keyword A query using window functions can contain the words `ORDER BY` twice, in positions that mean different things: ```sql SELECT employee_id, hire_date, ROW_NUMBER() OVER (ORDER BY hire_date) AS seniority_seq -- window ORDER BY FROM employees ORDER BY employee_id; -- query ORDER BY ``` The first belongs to the window definition and is bounded by the parentheses. The second belongs to the query and controls the order of the rows the client receives. Here rows are *numbered* by hire date and *returned* by employee id — a perfectly ordinary requirement, and one you cannot express with a single ordering. ## What the window ORDER BY actually controls It does exactly two things: 1. It gives rows in each partition a sequence, which is what positional functions need in order to say which row is first, previous, or next. 2. It makes frames meaningful, since a frame is expressed relative to the current row's position in that sequence. That is the whole of its remit. It is a computation input, not an output-formatting instruction. Nothing in the language says the rows must be *delivered* in that sequence. ## Why the output looks sorted anyway Computing a window with an `ORDER BY` usually involves arranging rows in that order, so the cheapest thing an engine can do is emit them in the order it already has. On a small table, in a simple plan, that is what typically happens — and that is precisely what makes the bug dangerous. It is a coincidence of the chosen plan, and plans change: - the table grows and the engine picks a different access path - an index is added, changing the order rows arrive in - the query is executed in parallel and results are merged - a partitioned window is computed per partition and the partitions are emitted in an arbitrary order - the engine is upgraded and rewrites the query differently None of those changes is a bug in the engine, because the query never asked for an output order. This is the same class of mistake as assuming `GROUP BY` returns groups in sorted order, or that rows come back in insertion order. ## The failure it produces The symptom is memorable: a report renders correctly for months, then one day the numbering column is out of sequence on screen even though every number is individually correct. Nothing about the window function broke — `ROW_NUMBER()` assigned exactly the values it should — the rows simply arrived shuffled, and a reader who scans a column of sequence numbers top to bottom sees nonsense. The fix is one line: add the query-level `ORDER BY` the statement always needed. A related and equally common variant is a paged query: the window function orders correctly, a row limit is applied, and without a query `ORDER BY` the "first page" is an arbitrary subset rather than the first rows of the sequence. ## Using both, deliberately Once you see them as independent, useful combinations follow: ```sql -- number by date, present alphabetically SELECT name, hire_date, ROW_NUMBER() OVER (ORDER BY hire_date) AS join_order FROM employees ORDER BY name; ``` You can also sort the output *by* a window result. The query's `ORDER BY` is evaluated after windows are computed, so it may reference the window column by alias, and standard SQL even allows writing the window function directly there: ```sql SELECT name, score, RANK() OVER (ORDER BY score DESC) AS score_rank FROM players ORDER BY score_rank; ``` Sorting by the alias is the readable form; repeating the whole `OVER` clause in `ORDER BY` is legal but duplicates the definition. ## Interaction with a row limit A row limit applies after the query's `ORDER BY`, and both apply after window functions are computed. So a limit never changes the values a window function produced — the window saw the full result set. Deciding *which* rows survive a limit is a separate matter from deciding which value each row carries. ## The habit to adopt Treat the two clauses as unrelated when you read a query: cover the parenthesised window definition with your hand and ask whether the statement still specifies an output order. If it does not, and the result is going anywhere a human or a downstream process will read positionally, the query is incomplete. Add the query `ORDER BY` even when the current output happens to look right — you are documenting a requirement, not correcting a symptom.