CODEWITHSUNDEEP
postgresqlaggregate-functions
user2724990
user2724990

Reputation: 51

How to add order by in string agg, when two columns are concatenated

SELECT string_agg( distinct a || '-' || b , ',' ORDER BY a,b) 
FROM table;

The above sql giving error

ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list

Upvotes: 5

Views: 4531

Answers (1)

klin
klin

Reputation: 121524

For the documentation:

If DISTINCT is specified in addition to an order_by_clause, then all the ORDER BY expressions must match regular arguments of the aggregate; that is, you cannot sort on an expression that is not included in the DISTINCT list.

So try

select string_agg(distinct a || '-' || b, ',' order by a || '-' || b)
from a_table;

or use distinct in a derived table:

select string_agg(a || '-' || b , ',' order by a, b)
from (
    select distinct a, b
    from a_table
    ) s;

Upvotes: 4

Related Questions