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

Question

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 name in the order given by rank. In mySQL I can do this:

  select id,           group_concat( image order by rank asc separator ',' )      from test  group by id; 

And the output would be:

 1 aaa,bbb,ccc 2 a,b,c 

Is there a way I can have this in postgresql?

If I try to use array_agg() the names will not show in the correct order and apparently I was not able to find a way to sort them. (I was using postgres 8.4 )

Answer 1

In PostgreSQL 8.4 you cannot explicitly order array_agg but you can work around it by ordering the rows passed into to the group/aggregate with a subquery:

SELECT id, array_to_string(array_agg(image), ',') FROM (SELECT * FROM test ORDER BY id, rank) x GROUP BY id; 

In PostgreSQL 9.0 aggregate expressions can have an ORDER BY clause:

SELECT id, array_to_string(array_agg(image ORDER BY rank), ',') FROM test GROUP BY id;