CODEWITHSUNDEEP
postgresql
Jun
Jun

Reputation: 91

PostgreSQL 8.4 - array_agg with order

|id     |profile     |data
|1      |name1       |2.1
|1      |name2       |400
|1      |name3       |200
|2      |name1       |3.4
|2      |name2       |350
|2      |name3       |500

This is what I have at the moment. When I ran this query:

SELECT id, ARRAY_AGG(profile), ARRAY_AGG(data) FROM "schema"."table" GROUP BY id;

I got:

|id     |array(profile)           |array(data)
|1      |{name1, name2, name3}    |{2.1, 400, 200}
|2      |{name2, name3, name1}    |{350, 500, 3.4}

I also tried to pre-sort

SELECT id, ARRAY_AGG(profile), ARRAY_AGG(data) FROM (SELECT * FROM "schema"."table" ORDER BY id) A GROUP BY id;

The data position match on both array but the format is not consistent. I wanted this result:

|id     |array(profile)           |array(data)
|1      |{name1, name2, name3}    |{2.1, 400, 200}
|2      |{name1, name2, name3}    |{3.4, 350, 500}

I am using PostgreSQL 8.4 so I cannot use array_agg(profile ORDER BY data).

Upvotes: 1

Views: 1283

Answers (1)

klin
klin

Reputation: 121634

As far as I remember Postgres 8.4, you can try to execute aggregates on a sorted derived table (unfortunately I cannot run 8.4 to verify this), e.g.:

select id, array_agg(profile), array_agg(data) 
from (
    select *
    from my_table
    order by id, profile) s
group by id
order by id;

Upvotes: 3

Related Questions