PostgreSQL: What's the best way to sort multiple array_agg() with different conditions?

Question

Conditions: PostgreSQL 8.4 (this would be too simple with 9.x)

Table:

  id  | gpa | class   | rank 
  ----+-----+---------+--------
  1   | 2.0 | english | low
  1   | 2.0 | math    | low
  1   | 2.0 | pe      | low
  1   | 2.0 | spanish | medium
  2   | 3.5 | english | high
  2   | 3.5 | history | high
  2   | 3.5 | art     | great
  2   | 3.5 | tech    | high
  3   | 4.0 | pe      | medium
  3   | 4.0 | spanish | high
  3   | 4.0 | english | great
  3   | 4.0 | art     | great

Wanted:

  id  | gpa |    great     |          high          | medium  | low 
  ----+-----+--------------+------------------------+---------+-----
  1   | 2.0 |              |                        | spanish | english, math, pe
  2   | 3.5 | art          | english, history, tech |         |
  3   | 4.0 | art, english | spanish                | pe      |

Current Method:

WITH details AS (
   select * from table order by rank, class
)
SELECT    id
        , gpa
        , array_to_string(array_agg(CASE WHEN rank='great'  THEN class END,', ')) as great
        , array_to_string(array_agg(CASE WHEN rank='high'   THEN class END,', ')) as high
        , array_to_string(array_agg(CASE WHEN rank='medium' THEN class END,', ')) as medium
        , array_to_string(array_agg(CASE WHEN rank='low'    THEN class END,', ')) as low
FROM      details
ORDER BY  gpa;

So I tried to put this as an example from what I'm doing - no I don't have an actual table with this structure that isn't much normalized, but I do have a subquery that produces something like this.

In actuality my array_agg() is also concatenating two fields (a word and a number) and i'm sorting by the number e.g.(array_agg(CASE ... THEN foo || ' - ' || bar), unfortunately my output is only mostly sorted. Perhaps I can improve this question if I have more time.

score 1 · Accepted Answer · 2012-02-13 07:07:53Z

1

When I execute this query:

with details AS (
   select * from table order by class
)
select t.id, t.gpa,
  (select array_to_string(array_agg(class),',')
   from details d
   where d.id = t.id and d.gpa = t.gpa and rank = 'great')
  as great,
  (select array_to_string(array_agg(class),',')
   from details d
   where d.id = t.id and d.gpa = t.gpa and rank = 'high')
  as high,
 (select array_to_string(array_agg(class),',')
  from details d
  where d.id = t.id and d.gpa = t.gpa and rank = 'medium')   
 as medium,
 (select array_to_string(array_agg(class),',')
  from details d
  where d.id = t.id and d.gpa = t.gpa and rank = 'low')
  as low
from table t
group by t.id, t.gpa
order by t.gpa

I get the results:

 id   gpa      great         high                 medium       low
----+-----+-------------+----------------------+----------+------------------
  1   2.0                                         spanish   english,math,pe
  2   3.5       art       english,history,tech
  3   4.0    art,english  spanish                 pe

I hope you serve.

UPDATE: Other option with a better performance.

select t.id, t.gpa,
       a.class as great,
       b.class as high,
       c.class as medium,
       d.class as low
from table t 
left join (select id, gpa, array_to_string(array_agg(class),',') as class
           from table
           where rank = 'great'
           group by id, gpa
          ) a on (a.id = t.id and a.gpa = t.gpa)
left join (select id, gpa, array_to_string(array_agg(class),',') as class
           from table
           where rank = 'high'
           group by id, gpa
          ) b on (b.id = t.id and b.gpa = t.gpa)
left join (select id, gpa, array_to_string(array_agg(class),',') as class
           from table
           where rank = 'medium'
           group by id, gpa
          ) c on (c.id = t.id and c.gpa = t.gpa)
 left join (select id, gpa, array_to_string(array_agg(class),',') as class
            from table
            where rank = 'low'
            group by id, gpa
           ) d on (d.id = t.id and d.gpa = t.gpa)          
 group by t.id, t.gpa, a.class, b.class, c.class, d.class
 order by t.gpa

edited Feb 13, 2012 at 7:07

answered Feb 12, 2012 at 12:36

user731136

Sign up to request clarification or add additional context in comments.

6 Comments

vol7ron Over a year ago

So this is something I was initially thinking about, but wouldn't it have a huge performance hit? It's running 4 queries for each record

user731136 Over a year ago

Hi @vol7ron, you're right. This isn't the best option due to huge performance hit. However I read that maybe you don't have permission to create a function. If you confirm that you have it, I can write other options with better performance.

user731136 Over a year ago

@vol7ron, I just update the answer with a second query with better performance.

vol7ron Over a year ago

that's what I thought of second - I think it's the best solution. I upvoted maniek, but in order have selected this as the answer (haven't tested it, but left join's should work)

user731136 Over a year ago

Thank you. With the second query I have obtained the same results as with the first.

|

maniek · Accepted Answer · 2012-02-12 01:25:24Z

1

Create a function like:

create or replace function sort(anyarray) returns anyarray language sql as 
$$
    select array_agg(x) from(
        select unnest($1) as x order by 1
    ) tab;
$$;

and then:

... 
array_to_string(sort(array_agg(CASE WHEN rank='great'  THEN class END,', '))) 
...

answered Feb 12, 2012 at 1:25

maniek

7,3252 gold badges24 silver badges44 bronze badges

1 Comment

vol7ron Over a year ago

So basically a subquery sort. not sure what permissions i have, I'll check if i can create a function

Collectives™ on Stack Overflow

PostgreSQL: What's the best way to sort multiple array_agg() with different conditions?

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related