PostgreSQL array_agg order for window functions

Question

The answer to my question was almost here: PostgreSQL array_agg order

Except that I wanted to array_agg over a window function:

 select distinct c.concept_name, 
        array_agg(c2.vocabulary_id||':'||c2.concept_name 
                  order by c2.vocabulary_id, c2.concept_name) 
            over (partition by ca.min_levels_of_separation), 
        ca.min_levels_of_separation
 from concept c
 join concept_ancestor ca on c.concept_id = ca.descendant_concept_id 
      and max_levels_of_separation > 0
 join concept c2 on ca.ancestor_concept_id = c2.concept_id 
 where 
 c.concept_code = '44054006'
 order by min_levels_of_separation;

So, maybe this will work in some future version, but I get this error

 ERROR:  aggregate ORDER BY is not implemented for window functions
 LINE 2: select distinct c.concept_name, array_agg(c2.vocabulary_id||...
                                    ^

I should probably be selecting from a subquery like the first answer to the quoted question above suggests. I was hoping for something as simple as the order by (in that question's second answer). Or maybe I'm just being lazy about the query and should be doing a group by instead of select distinct.

I did try putting the order by in the windowing function (over (partition by ca.min_levels_of_separation order by c2.vocabulary_id, c2.concept_name)), but I get these sort of repeated rows that way:

 "Type 2 diabetes mellitus";"{"MedDRA:Diabetes mellitus"}";1
 "Type 2 diabetes mellitus";"{"MedDRA:Diabetes mellitus","MedDRA:Diabetes mellitus (incl subtypes)"}";1
 "Type 2 diabetes mellitus";"{"MedDRA:Diabetes mellitus","MedDRA:Diabetes mellitus (incl subtypes)","SNOMED:Diabetes mellitus"}";1

(btw: http://www.ohdsi.org/ if you happen to be curious about where I got the medical vocabulary tables)

score 2 · Accepted Answer · answered Mar 14 '16 at 10:32

Yes, it does look like I was being muddle-headed and didn't need the window function. This seems to work:

 select  c.concept_name, 
         array_agg(c2.vocabulary_id||':'||c2.concept_name 
                   order by c2.vocabulary_id, c2.concept_name), 
         ca.min_levels_of_separation
 from concept c
 join concept_ancestor ca on c.concept_id = ca.descendant_concept_id 
      and max_levels_of_separation > 0
 join concept c2 on ca.ancestor_concept_id = c2.concept_id
 where c.concept_code = '44054006'
 group by c.concept_name, ca.min_levels_of_separation
 order by min_levels_of_separation

I won't accept my answer for a while since it just avoids the question instead of actually answering it, and someone might have something more useful to say on the matter.

Looks totally fine at a quick glance – pumbo Mar 15 '16 at 04:31 — pumbo, Mar 15 '16 at 04:31

score 0 · Answer 2 · answered Mar 14 '16 at 11:42

0

like this :

select distinct c.concept_name, 
    array_agg(c2.vocabulary_id||':'||c2.concept_name ) over (partition by ca.min_levels_of_separation  order by c2.vocabulary_id, c2.concept_name), 
    ca.min_levels_of_separation
from concept c
join concept_ancestor ca on c.concept_id = ca.descendant_concept_id 
  and max_levels_of_separation > 0
join concept c2 on ca.ancestor_concept_id = c2.concept_id 
where 
c.concept_code = '44054006'
order by min_levels_of_separation;

answered Mar 14 '16 at 11:42

Mesbah Gueffaf

518
1
7
21

Sorry, I should have said I tried that. It gives me sort of repeated rows. I'll edit the question to show what I mean (can't format it in a comment.) – Sigfried Mar 14 '16 at 11:46

PostgreSQL array_agg order for window functions

2 Answers2