160

Table 'animals':

animal_name animal_type
Tom         Cat
Jerry       Mouse
Kermit      Frog

Query:

SELECT 
array_to_string(array_agg(animal_name),';') animal_names,
array_to_string(array_agg(animal_type),';') animal_types
FROM animals;

Expected result:

Tom;Jerry;Kerimt, Cat;Mouse;Frog
OR
Tom;Kerimt;Jerry, Cat;Frog;Mouse

Can I be sure that order in first aggregate function will always be the same as in second. I mean I would't like to get:

Tom;Jerry;Kermit, Frog;Mouse,Cat
MWiesner
  • 8,868
  • 11
  • 36
  • 70
Olo
  • 1,603
  • 2
  • 11
  • 4
  • 8
    If you are on 9.0 you can replace the nested calls with a single `string_agg()` –  Sep 06 '11 at 09:15

4 Answers4

457

Use an ORDER BY, like this example from the manual:

SELECT array_agg(a ORDER BY b DESC) FROM table;
Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
  • 52
    Note: `ORDER BY` in `array_agg` is introduced in PostgreSQL 9 – UlfR Sep 06 '11 at 10:00
  • 7
    Much better than the accepted answer assuming PostgreSQL 9+. – Erik Feb 09 '14 at 05:15
  • 2
    Could you please give sample with ORDER BY for select: SELECT array_agg(animal_name), array_agg(animal_type) FROM animals; ? – Grigory Kislin Nov 12 '14 at 17:41
  • I suppose SELECT array_agg(name ORDER BY pk), array_agg(type ORDER BY pk) FROM animal; is the answer by could it be simple? – Grigory Kislin Nov 12 '14 at 17:53
  • 17
    Note that this does *not* work for `array_agg(DISTINCT a ORDER BY b)` – cerd Mar 24 '16 at 19:35
  • Surely arrays of different aggregations in a single SELECT are correlated, so specifying an explicit order is not required for the OP. – beldaz Nov 03 '17 at 00:32
  • @cerd what do you suggest in case of DISTINCT? I want the a to be ordered – Dejell Dec 18 '17 at 09:18
  • in that case you need to figure out what you want and explain it clearly. `DISTINCT a ORDER BY b` is unclear. eg: `(1,2),(2,1),(1,0)` – Jasen Jul 11 '18 at 00:10
  • 3
    When using for multiple columns you have to add parentheses: `array_agg((a, b, c) ORDER BY b)` – bennos Oct 17 '18 at 09:12
  • This seems like a good place to mention the somewhat outlandish syntax for functions like `string_agg` which take multiple arguments: `SELECT string_agg(a, '|' ORDER BY b DESC)` – LondonRob Jul 19 '20 at 00:33
  • You saved my day!! I was going through various other solutions but I was looking for this! Thanks! – yatish h r May 11 '22 at 16:30
32

If you are on a PostgreSQL version < 9.0 then:

From: http://www.postgresql.org/docs/8.4/static/functions-aggregate.html

In the current implementation, the order of the input is in principle unspecified. Supplying the input values from a sorted subquery will usually work, however. For example:

SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

So in your case you would write:

SELECT
array_to_string(array_agg(animal_name),';') animal_names,
array_to_string(array_agg(animal_type),';') animal_types
FROM (SELECT animal_name, animal_type FROM animals) AS x;

The input to the array_agg would then be unordered but it would be the same in both columns. And if you like you could add an ORDER BY clause to the subquery.

UlfR
  • 4,175
  • 29
  • 45
8

According to Tom Lane:

... If I read it right, the OP wants to be sure that the two aggregate functions will see the data in the *same* unspecified order. I think that's a pretty safe assumption. The server would have to go way out of its way to do differently, and it doesn't.

... So it is documented behavior that an aggregate without its own ORDER BY will see the rows in whatever order the FROM clause supplies them.

So I think it's fine to assume that all the aggregates, none of which uses ORDER BY, in your query will see input data in the same order. The order itself is unspecified though (which depends on the order the FROM clause supplies rows).

Source: PostgreSQL mailing list

ebk
  • 586
  • 5
  • 11
  • 1
    This is the only answer whose author actually bothered to understand the question, namely whether the *two aggregates* would have the *same* order. – cantordust Jul 14 '21 at 11:55
  • 1
    I note that, in the linked mail thread, Tom Lane explicitly recommends *against* adding an `ORDER BY` statement to each `array_agg`, for efficiency reasons. – Damien Nov 03 '21 at 11:31
2

Do this:

SELECT 
    array_to_string(array_agg(animal_name order by animal_name),';') animal_names,
    array_to_string(array_agg(animal_type order by animal_type),';') animal_types
FROM 
    animals;
lpizzinidev
  • 12,741
  • 2
  • 10
  • 29
Sikh
  • 37
  • 1