Search code examples
sqlpostgresqlwindow-functionssql-null

Get the most recent and not null row

I have the following table:

id timestamp attribute-1 attribute-2
1 8/12 A
1 8/13 B
2 8/12 A
2 8/13 C B
2 8/14 B
3 8/12 B
3 8/14 C C

And would like to create a new table with the most updated attributes for each id, which means the most recent row for each id as long as it has both att-1 and att-2, if it doesn't I want to take the att from a previous row. should be like that:

id attribute-1 attribute-2
1 B A
2 B B
3 C C
  • The timestamp is a real psql timestamp
Solution

  • You can use FIRST_VALUE() window function:

    SELECT DISTINCT id, 
       FIRST_VALUE(attribute1) OVER (PARTITION BY id ORDER BY attribute1 IS NULL, timestamp DESC) attribute1,
       FIRST_VALUE(attribute2) OVER (PARTITION BY id ORDER BY attribute2 IS NULL, timestamp DESC) attribute2
FROM tablename;

    See the demo.