Im asking for PostgreSQL specifically, but answers for other popular SQL implementations are appreciated as well.
Given an sql query with multiple aggregates, especially array_agg
, is the order of the aggregated values deterministic?
Example:
SELECT ARRAY_AGG(columnA), ARRAY_AGG(columnB) FROM myTable
GROUP BY columnC
Can I rely on both arrays to have the same order, meaning values at position i in both arrays will belong to the same source row?
I can't find anything about this in the docs and I'm unsure because I've read that parallelization could be used in calculating aggregates, which I'm afraid could possibly result in non-deterministic orders.
CodePudding user response:
The order is never deterministic if you don't provide an order by
So if you need a specific order, then specify it:
SELECT ARRAY_AGG(columnA order by some_sort_column),
ARRAY_AGG(columnB order by some_sort_column)
FROM myTable
GROUP BY columnC