Sort a text aggregate created with array_agg in postgresql

user_335435 0
Viewed 29388

I have a table in postgresql. The following table "animals" will do to explain my problem:

name
------
tiger
cat
dog

Now I am using the following query:

SELECT
    array_to_string(array_agg("name"), ', ')
FROM
    animals;

The result is: "tiger, cat, dog". But I would like to sort the aggregate, before it is converted into a string. So this is the result I am hoping for:

"cat, dog, tiger".

So how can I sort an string array in postgresql 8.4 before converting it to a string. ORDER BY on the row "name" does not work and the built-in sort function processes only integer values.

Anyone a good idea, how to solve this in pure SQL?

Thanx a lot Richard

postgresql
6 Answers

To update on this question, Snowflake has implemented array sorting:

SELECT
    array_sort(array_agg("name")
FROM
    animals;

Can also use array_sort_by to sort an object

user_58390070
Related