Aggregate strings in descending order in a PostgreSQL query

Question

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.

Answer 1

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()?