PostgreSQL array_agg order

postgresql array-agg
Olo picture Olo · Sep 6, 2011 · Viewed 77.6k times · Source

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

Answer

Frank Heikens picture Frank Heikens · Sep 6, 2011

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

SELECT array_agg(a ORDER BY b DESC) FROM table;

Related questions

How to remove duplicates, which are generated with array_agg postgres function

Does anyone an idea how to rewrite following SQL query to generate results, that would contains only one occurrence of name? (results grouped by user). The query SELECT array_to_string(array_agg(CONCAT(u.firstname, ' ', u.lastname)), …

Read More
how to make array_agg() work like group_concat() from mySQL

So I have this table: create table test ( id integer, rank integer, image varchar(30) ); Then some values: id | rank | image ---+------+------- 1 | 2 | bbb 1 | 3 | ccc 1 | 1 | aaa 2 | 3 | c 2 | 1 | a 2 | 2 | b I want to group them by id and concatenate the image …

Read More
How to aggregate two PostgreSQL columns to an array separated by brackets

I would like to concatenate two columns using a group-by query resulting in an array separed with brackets. I know this question is related to this question, but as usual my use-case is a little different. A simple example (also …

Read More