4

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?

burrscurr
  • 3
  • 3
belfastcowboy24
  • 147
  • 2
  • 7
  • 1
    It depends on your definition of "first occurrence". Rows are returned in whatever order the executor decides to return them. The order you inserted values isn't stored in the table. But if you said that the lowest value counts as the first, you could just use `SELECT category, min(value) FROM table GROUP BY category;` – Thom Brown May 18 '20 at 17:04

1 Answers1

18

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)
Haleemur Ali
  • 26,718
  • 5
  • 61
  • 85
  • Fantastic solution, thank you for that! Another option if dealing with strings - or willing to cast - is to use STRING_AGG which will ignore the NULLs. – somada141 Nov 01 '20 at 08:59
  • Just tested this, and the array result is NULL when the array contains all NULLs. I was worried it would throw an error accessing `[1]` on an empty array, but it thankfully returns NULL instead. – WebWanderer Apr 20 '22 at 20:26