Home > database >  Have multiple aggregations in a query always the same order?
Have multiple aggregations in a query always the same order?

Time:02-10

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
  • Related