SQLite Forum

How can I fill a NULL with previous Data ?
Login
You are of course going to need an ordering. Whether it's a datetime, id, one column, more, etc. Otherwise this doesn't make sense. For the sake of my example here I'm going to use a single column unique value ordering. We can adjust the answer when you let us know how you have it ordered.

This might not be the best, but it seems to work. Of course, you could always just do this in your controlling application rather than figuring out how to get the query engine to do it.

create table weather_table (ordering_column integer primary key, IP_24_Temperature real, IP_24_Humidity real);

insert into weather_table values (1, 16.5, null), (2, null, null), (3, null, null), (4, 16.8, 58), (5, null, null), (6, null, null), (7, null, null), (8, null, 60), (9, null, null), (10, 16.5, 61);

with recursive foo (ordering_column, IP_24_Temperature, IP_24_Humidity) as
(
select
min(ordering_column),
coalesce(IP_24_Temperature, 0), --<value you want if the first temp is null>
coalesce(IP_24_Humidity, 0) --<value you want if the first humidity is null>
from weather_table

union all

select
weather_table.ordering_column,
coalesce(weather_table.IP_24_Temperature, foo.IP_24_Temperature),
coalesce(weather_table.IP_24_Humidity, foo.IP_24_Humidity)
from
foo inner join weather_table
on weather_table.ordering_column = (
    select ordering_column
    from weather_table
    where ordering_column > foo.ordering_column
    order by weather_table.ordering_column
    limit 1
)
)
select * from foo
order by ordering_column;