sqlpostgresqlaggregate-functionspostgresql-8.4

Aggregate strings in descending order in a PostgreSQL query

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.

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: