In addition to the question How to concatenate strings of a string field in a PostgreSQL 'group by' query?
How can I sort employee in descending order?
I am using PostgreSQL 8.4 which doesn't support string_agg(). I've tried to use the following, but it isn't supported:
array_to_string(array_agg(employee ORDER BY employee DESC), ',')
I'd appreciate any hint to the right answer.
In PostgreSQL 9.0 or later you can order elements inside aggregate functions:
SELECT company_id, string_agg(employee, ', ' ORDER BY company_id DESC)::text
FROM tbl
GROUP BY 1;
Neither string_agg() nor that ORDER BY are available for PostgreSQL 8.4. You have to pre-order values to be aggregated. Use a subselect or CTE (pg 8.4+) for that:
SELECT company_id, array_to_string(array_agg(employee), ', ')
FROM (SELECT * FROM tbl ORDER BY company_id, employee DESC) x
GROUP BY 1;
I order by company_id in addition as that should speed up the subsequent aggregation.
Less elegant, but faster. (Still true for Postgres 14.)
See:
