Window function to select the most recent non-null value in Postgresql doesn't work

Question

I have a simple dataset like this:

CREATE TABLE test (ts timestamp, email varchar, title varchar, hobby varchar);

insert into test values
('2017-01-01', '[email protected]', 'blah', null),
('2017-01-02', '[email protected]', null, 'expected hobby'),
('2017-01-03', '[email protected]', 'expected title', null),
('2017-01-04', '[email protected]', null, null);

And I'd like to select the most recent non-null value of title and hobby per email, using a window function. These are 'expected title' and 'expected hobby'. I'm trying this:

SELECT DISTINCT
    email,
    first_value(title) OVER (partition by email order by ts desc nulls last),
    first_value(hobby) OVER (partition by email order by ts desc nulls last)
from test

But it doesn't seem to work. Here's a sql fiddle: http://sqlfiddle.com/#!17/6d681/2

Any idea why?

I may have solved this with: first_value(title) OVER (partition by email order by case when title is not null then ts end desc nulls last), — LittleBobbyTables
– LittleBobbyTables, Commented Mar 13, 2018 at 15:00

LittleBobbyTables · Accepted Answer · 2018-03-13 20:55:32Z

2

I fixed it using a case statement to eliminate the nulls:

SELECT DISTINCT
    email,
    first_value(title) OVER (partition by email order by case when title is not null then ts end desc nulls last),
    first_value(hobby) OVER (partition by email order by case when hobby is not null then ts end desc nulls last),
from test

answered Mar 13, 2018 at 20:55

LittleBobbyTables

4,52310 gold badges43 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

FatFreddy · Accepted Answer · 2018-03-14 12:30:28Z

1

your order does not work as excpected

 order by ts desc nulls last

because ts has no nulls, something like that should also work (as your solution)

SELECT distinct 
    email,
    first_value(title) OVER (partition by email order by (ts || title) desc nulls last ROWS  BETWEEN  UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING ) as title,
    first_value(hobby) OVER (partition by email order by (ts || hobby) desc nulls last ROWS  BETWEEN  UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING ) as hobby
from test

a concat with null results in null, so ts || null results in null

answered Mar 14, 2018 at 12:30

FatFreddy

1,2101 gold badge10 silver badges17 bronze badges

Collectives™ on Stack Overflow

Window function to select the most recent non-null value in Postgresql doesn't work

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related