CODEWITHSUNDEEP
postgresql
Richard
Richard

Reputation: 525

Sort a text aggregate created with array_agg in postgresql

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

Upvotes: 31

Views: 32739

Answers (6)

Mike T
Mike T

Reputation: 43762

Use an ORDER BY clause in an aggregate expression:

SELECT
    array_to_string(array_agg(name ORDER BY name), ', ')
FROM
    animals;

Also, for your specific purpose, you can use string_agg to simplify your query:

SELECT
    string_agg(name, ', ' ORDER BY name)
FROM
    animals;

Upvotes: 67

cacti5
cacti5

Reputation: 2106

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

Upvotes: 0

jnas
jnas

Reputation: 1026

Still, for version 8.4, using the solution suggested by Matthew Wood, if you need to do a grouping in the outer query, the inner query should be sorted, too, for the sorting to be consistent.

SELECT family, array_agg(animal_name)
FROM (
    SELECT family, "name" AS animal_name
    FROM animals
    ORDER BY family, "name"
) AS sorted_animals
group by family;

Upvotes: 0

Joey Adams
Joey Adams

Reputation: 43410

Although Matthew Wood's answer is better for your case, here is a way to sort arrays in PostgreSQL 8.4 and up:

SELECT array(
    SELECT unnest(array[3,2,1]) AS x ORDER BY x
);

Knowing the array and unnest functions can be handy, since it also lets you do things like "map" over an array:

SELECT array(
    SELECT x*x FROM (SELECT unnest(array[1,2,3]) AS x) as subquery
);

Again, this can be yours for the price of PostgreSQL 8.4 .

Upvotes: 4

Matthew Wood
Matthew Wood

Reputation: 16427

This will be available in PostgreSQL 9.0:

http://www.postgresql.org/docs/9.0/static/release-9-0.html, Section E.1.3.6.1. Aggregates

In the meantime, you could do something like this which may solve the problem (albeit clunky):

SELECT array_agg(animal_name)
FROM (
    SELECT "name" AS animal_name
    FROM animals
    ORDER BY "name"
) AS sorted_animals;

Upvotes: 15

dave
dave

Reputation: 1364

Have you tried to use generate_series() on the array, and then do a SELECT...ORDER BY on that result (or just nest it inside of the SELECT) before you convert it to a string?

Upvotes: 0

Related Questions