How to use LAST_VALUE in PostgreSQL?

I have a little table to try to understand how the LAST_VALUE function works in PostgreSQL. It looks like this:

 id | value
----+--------
  0 | A
  1 | B
  2 | C
  3 | D
  4 | E
  5 | [null]
  6 | F

What I want to do is to use LAST_VALUE to fill the NULL value with the precedent non-NULL value, so the result should be this:

 id | value
----+--------
  0 | A
  1 | B
  2 | C
  3 | D
  4 | E
  5 | E
  6 | F

The query I tried to accomplish that is:

SELECT LAST_VALUE(value)
OVER (PARTITION BY id ORDER BY case WHEN value IS NULL THEN 0 ELSE 1 END ASC)
FROM test;

From what I understand of the LAST_VALUE function, it takes all the rows before the current one as a window, sorts them following the ORDER By thing and then returns the last row of the window. With my ORDER BY, all the rows containing a NULL should be put on top of the window, so LAST_VALUE should return the last non NULL value. But it doesn’t.

I am clearly missing something. Please help.

Answer

So, thanks to Jeremy’s explanations and another post (PostgreSQL last_value ignore nulls) I finally figured it out:

SELECT id, value, first_value(value) OVER (partition by t.isnull) AS new_val
FROM(
    SELECT id, value, SUM (CASE WHEN value IS NOT NULL THEN 1 END) OVER (ORDER BY id) AS isnull
    FROM test) t;

This query returns the result I expected.

Advertisement

Answer