Why can STRING_AGG produce a different element order each run, and where does its ORDER BY go?
answer
- Groups are bags: rows carry no order
- Sorting the outer result is too late
- The aggregate needs its own ordering clause
- WITHIN GROUP, or ORDER BY inside the arguments
- Add a tiebreaker for a total order
basics
~20 sA group's rows have no inherent order, so concatenation order is unspecified unless you order inside the aggregate — WITHIN GROUP (ORDER BY …) or an ORDER BY in the argument list. The query's own ORDER BY sorts result rows, not the elements inside one value.
solid answer
~50 sString and array aggregation are *ordered-set* aggregates: the result depends on the sequence in which the group's rows reach the function, and SQL gives a group no inherent sequence. Whatever order you observe comes from the plan — a different join method, an index change, or parallelism can reorder it silently, so an unordered `STRING_AGG` is a latent bug, not merely untidy output. The fix is to attach the ordering to the aggregate itself: `STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY hired_on)` in the standard/Oracle/SQL Server spelling, or `string_agg(name, ', ' ORDER BY hired_on)` in PostgreSQL and `GROUP_CONCAT(name ORDER BY hired_on)` in MySQL. Note the sort key need not be the aggregated column — ordering names by hire date is common. The outer `ORDER BY` cannot help: it sorts the rows the query returns, long after each group has already been folded into one value.
code
sql · 5 lines-- Wrong: order of names inside each list is unspecified
SELECT department_id, STRING_AGG(last_name, ', ') AS staff
FROM employees
GROUP BY department_id
ORDER BY department_id; -- sorts rows, not list elementsgo deeper
Remember that you must state the order explicitly inside the aggregate; do not assume values come out in the order they were inserted.
Explain why a group has no inherent row order, name both ordering spellings, and show that grouping happens before the final sort so the outer ORDER BY cannot reach inside a value.
Treat an unordered concatenation as a correctness defect: plan changes, added indexes, and parallel scans silently reorder it, and ties still break determinism without a unique tiebreaker.
Own the standard that output which is diffed, hashed, or contract-tested must be totally ordered by construction, and decide where that guarantee lives — in the query, or in the consumer that reassembles the list.
## Why the order is undefined by default When a query groups rows, the group is a *bag* of rows — a collection with no defined sequence. Aggregates like `SUM` and `COUNT` do not care, because addition and counting are order-independent: any sequence gives the same answer. String and array aggregation are different. `'a, b'` and `'b, a'` are different values, so the result depends on the order in which rows are handed to the function. The standard calls these *ordered-set aggregates* and gives them dedicated ordering syntax precisely because there is nothing else for them to fall back on. This is the trap: in practice you almost always *see* some order, and it often looks sensible — insertion order, or primary-key order, or whatever order the index scan produced. None of that is guaranteed. The order is an artifact of the execution plan, and plans change when statistics change, when an index is added or dropped, when the join method flips, or when the engine parallelises the scan. A report that has looked right for a year can start emitting differently-ordered lists after an unrelated deployment, and a test that compares against a golden string will start failing for no visible reason. ## Where the ordering clause goes There are two spellings, and which one you use is decided by the engine, not by taste. **`WITHIN GROUP (ORDER BY …)`** — the standard form, used by `LISTAGG` in Oracle and Db2 and by `STRING_AGG` in SQL Server: ```sql SELECT department_id, LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY hire_date, last_name) AS staff FROM employees GROUP BY department_id; ``` **`ORDER BY` inside the argument list** — used by PostgreSQL's `string_agg` and `array_agg`, and by MySQL's `GROUP_CONCAT`: ```sql -- PostgreSQL SELECT department_id, string_agg(last_name, ', ' ORDER BY hire_date, last_name) AS staff FROM employees GROUP BY department_id; -- MySQL SELECT department_id, GROUP_CONCAT(last_name ORDER BY hire_date, last_name SEPARATOR ', ') AS staff FROM employees GROUP BY department_id; ``` In every spelling the sort key is a list, can contain several columns, accepts `ASC`/`DESC`, and — importantly — need not be the column being aggregated. Ordering employee names by hire date, or ordering tags by a display-priority column, is entirely normal. The sort applies **within each group independently**: every group is ordered by the same rule, but the orderings do not interact. ## What the outer ORDER BY does and does not do A very common wrong answer is "just add `ORDER BY hire_date` to the query." Follow the logical evaluation of the statement: grouping and aggregation happen before the final sort, so by the time the outer `ORDER BY` runs, each group has already been folded into one string. The outer clause can only sort the one-row-per-group result set — it decides which department appears first, never which name appears first inside a department's string. A subtler variant of the same mistake is sorting a derived table and aggregating over it: ```sql -- Not reliable: the inner ORDER BY does not bind the aggregate SELECT department_id, string_agg(last_name, ', ') FROM (SELECT * FROM employees ORDER BY hire_date) e GROUP BY department_id; ``` This frequently *appears* to work, which is what makes it dangerous. An ordering in a subquery is not part of the subquery's contract to its consumer; the engine is free to re-sort, re-scan, or parallelise, and the aggregate's input order is not preserved by anything in the language. The ordering has to be attached to the aggregate. ## Ties, NULLs, and determinism Deterministic output needs a *total* order. If two rows tie on the sort key, their relative order is again unspecified, so add a tiebreaker — typically a unique key — when the exact string matters (for a checksum, a golden-file test, or a diffable export). NULLs in the sort key sort as a group at one end; the standard's `NULLS FIRST` / `NULLS LAST` modifiers, where the engine supports them in this position, let you pin which end. ## The same rule covers arrays `ARRAY_AGG` is an ordered-set aggregate for exactly the same reason, and takes the same ordering clause: `array_agg(tag ORDER BY tag)`. If the array's element order carries meaning — a timeline, a ranked list, a path — the ordering clause is not optional there either.
- Does the sort key inside the aggregate have to be the column being concatenated?No. The ordering clause takes its own expression list, so you can concatenate names ordered by hire date or by a display-priority column. It also accepts several keys with ASC/DESC per key, and the sort applies independently within each group.
- Two rows tie on the sort key — is the output still deterministic?No. Ties leave the relative order of those elements unspecified, so the string can vary between runs. Add a tiebreaker column that is unique within the group — usually the primary key — whenever the exact string is compared, hashed, or checked into a test.
- Why is sorting a derived table and aggregating over it not a reliable substitute?A subquery's ORDER BY is not a contract to its consumer: the engine may re-sort, re-scan, or parallelise the input, and nothing in the language preserves that order into the aggregate. It often appears to work, which is exactly what makes it a latent bug.
saying these in an interview costs you the question
- Says the outer ORDER BY controls the concatenation order
- Assumes values come out in insertion or primary-key order
- Believes an ORDER BY in a subquery binds the aggregate
- Thinks the sort key must be the aggregated column
- Calls unordered output a cosmetic issue rather than a bug