title: Coalescing aggregate of fields in postgres
body: <p>I'm trying to group by a <code>name</code> field, but with a 'coalescing aggregate' of the other values a row might have. Here's the data:</p>
  
  <pre><code>name        | jurisdiction    | db_from
  ------------|-----------------|----------------
  my station    NULL              2017-06-21 8:01
  my station    my jurisdiction   2017-06-21 8:00
  my station    old data          2017-06-21 7:59
  </code></pre>
  
  <p>I want to wind up with:</p>
  
  <pre><code>name        | jurisdiction    
  ------------|-----------------
  my station    my jurisdiction  
  </code></pre>
  
  <p>Here's what I've come up with:</p>
  
  <pre><code>WITH _stations AS (
      SELECT * FROM config.stations x ORDER BY x.db_from DESC
  )SELECT 
      x."name",
      (array_remove(array_agg(x.jurisdiction), NULL))[1] as jurisdiction
  FROM _stations x
  GROUP BY
      x."name";
  </code></pre>
  
  <p>Some of the values of the <code>jurisdiction</code> fields may be null and I'm trying to take the most recent one that's not null.</p>
  
  <p>I know I shouldn't be relying on the order of the CTE, but it seems to be working for now.</p>