PostgreSQL: get first non null value per group

Question

I'd like to obtain is the first ocurrence of non-null value per category. If there are just null values, the result of this category shall be NULL.

For a table like this:

Category    Value
1           NULL
1           1922
2           23
2           99
3           NULL
3           NULL

the result should be

Category    Value
1           1922
2           23
3           NULL

How can this be achieved using postgres?

Answer 1

Unfortunately the two features that would make this trivial are not implemented in postgresql

• IGNORE NULLS in FIRST_VALUE, LAST_VALUE
• FILTER clause in non-aggregate window functions

However, you can hack the desired result using groupby & array_agg , which does support the FILTER clause, and then pick the first element using square-bracket syntax. (recall that postgresql array indexing starts with 1)

Also, I would advise that you provide an explicit ordering for the aggregation step. Otherwise the value that ends up as the first element would depend on the query plan & physical data layout of the underlying table.

WITH vals (category, val) AS ( VALUES
  (1,NULL),
  (1,1922),
  (2,23),
  (2,99),
  (3,NULL),
  (3,NULL)
)
SELECT
  category
, (ARRAY_AGG(val) FILTER (WHERE val IS NOT NULL))[1]
FROM vals
GROUP BY 1

produces the following output:

 category | array_agg
----------+-----------
        1 |      1922
        3 |
        2 |        23
(3 rows)