title: Postgres aggregate function for selecting best / first element in a group
body: <p>I have a table of elements with a number of items that need de-duping based on a priority. The following is a grossly simplified but representative example:</p>
  
  <pre><code>sophia=&gt; select * from numbers order by value, priority;
   value | priority | label 
  -------+----------+-------
       1 |        1 | One
       1 |        2 | Eins
       2 |        1 | Two
       2 |        2 | Zwei
       3 |        2 | Drei
       4 |        1 | Four
       4 |        2 | Vier
  (7 rows)
  </code></pre>
  
  <p>I want to restrict this to returning only a single row per number. Easy enough, I can use the first() aggregate function detailed in <a href="https://wiki.postgresql.org/wiki/First/last_(aggregate)" rel="nofollow noreferrer">https://wiki.postgresql.org/wiki/First/last_(aggregate)</a></p>
  
  <pre><code>sophia=&gt; select value, first(label) from numbers group by value order by value;
   value | first 
  -------+-------
       1 | One
       2 | Two
       3 | Drei
       4 | Four
  (4 rows)
  
  sophia=&gt; 
  </code></pre>
  
  <p>The problem with this is that the order isn't well defined so if the DB rows were inserted in a different order, I might get this:</p>
  
  <pre><code>sophia=&gt; select value, first(label) from numbers group by value order by value;
   value | first 
  -------+-------
       1 | Eins
       2 | Zwei
       3 | Drei
       4 | Vier
  (4 rows)
  </code></pre>
  
  <p>Of course, the solution to that also seems simple, in that I could just do an order by:</p>
  
  <pre><code>sophia=&gt; select value, first(label) from (select * from numbers order by priority) foo group by value order by value;
   value | first 
  -------+-------
       1 | One
       2 | Two
       3 | Drei
       4 | Four
  (4 rows)
  
  sophia=&gt; 
  </code></pre>
  
  <p>However, the problem here is that the query optimizer is free to discard the order by rules in subqueries meaning that this doesn't always work and breaks in random nasty places.</p>
  
  <p>I have a solution that I'm currently using in a handful of places that relies on array_agg.</p>
  
  <pre><code>sophia=&gt; select value, (array_agg(label order by priority))[1] as best_label from numbers group by value;
   value | best_label 
  -------+------------
       1 | One
       2 | Two
       3 | Drei
       4 | Four
  (4 rows)
  
  sophia=&gt; 
  </code></pre>
  
  <p>This provides robust ordering but involves creating a bunch of extra arrays during query time that just get thrown away and hence the performance on larger datasets rather sucks.</p>
  
  <p>So the question is, is there a better, cleaner, faster way of dealing with this?</p>