Coalescing aggregate of fields in postgres

Question

I'm trying to group by a name field, but with a 'coalescing aggregate' of the other values a row might have. Here's the data:

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

I want to wind up with:

name        | jurisdiction    
------------|-----------------
my station    my jurisdiction  

Here's what I've come up with:

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";

Some of the values of the jurisdiction fields may be null and I'm trying to take the most recent one that's not null.

I know I shouldn't be relying on the order of the CTE, but it seems to be working for now.

Answer 1

It took me a bit if digging to find this so I hope it helps. Let me know if this isn't the best way to solve the problem:

SELECT 
    x."name",
    (array_remove(array_agg(x.jurisdiction ORDER BY x.db_from DESC), NULL))[1] as jurisdiction
FROM config.stations x
GROUP BY x."name"