Aggregate strings in descending order in a PostgreSQL query
aggregate-functions, postgresql, postgresql-8.4, sql
Solution
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:
- Concatenate multiple result rows of one column into one, group by another column
- Alternatives to array_agg()?
Problem
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.