Skip to content
Advertisement

Function to REPLACE* last previous known value for NULL

I want to fill the NULL values with the last given value for that column. A small sample of the data:

In this example, the NULL values should be 1089066 until I reach the next non-NULL value.

I tried the answer given in this response, but to no avail. Any help would be appreciated, thank you!

EDIT: Sorry, I got sidetracked with trying to return the last value that I forgot my ultimate goal, which is to replace the NULL values with the previous known value.

Therefore the query should be

Advertisement

Answer

Assuming the number you have is always increasing, you can use MAX aggregate over a window:

If the number may decrease, the query becomes a little bit more complex as we need to mark the rows that have nulls as belonging to the same group as the last one without a null first:

Here’s a working demo on dbfiddle, it returns the same data with ever increasing amount, but if you change the number for 08-17 to be lower than that of 08-16, you’ll see MAX(…) method producing wrong results.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement