title: PostgreSQL array_agg order for window functions
body: <p>The answer to my question was almost here: <a href="https://stackoverflow.com/questions/7317475/postgresql-array-agg-order">PostgreSQL array_agg order</a></p>
  
  <p>Except that I wanted to array_agg over a window function:</p>
  
  <pre><code> 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 &gt; 0
   join concept c2 on ca.ancestor_concept_id = c2.concept_id 
   where 
   c.concept_code = '44054006'
   order by min_levels_of_separation;
  </code></pre>
  
  <p>So, maybe this will work in some future version, but I get this error</p>
  
  <pre><code> ERROR:  aggregate ORDER BY is not implemented for window functions
   LINE 2: select distinct c.concept_name, array_agg(c2.vocabulary_id||...
                                      ^
  </code></pre>
  
  <p>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 <code>group by</code> instead of <code>select distinct</code>.</p>
  
  <p>I did try putting the order by in the windowing function (<code>over (partition by ca.min_levels_of_separation order by c2.vocabulary_id, c2.concept_name)</code>), but I get these sort of repeated rows that way:</p>
  
  <pre><code> "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
  </code></pre>
  
  <p>(btw: <a href="http://www.ohdsi.org/" rel="nofollow noreferrer">http://www.ohdsi.org/</a> if you happen to be curious about where I got the medical vocabulary tables)</p>