Replace null values with previous values in timesent-CodePudding

I am trying to replace null values with previous available record. i try to search answer for this query on stakeoverflow however, those answers are quite confusing hence looking for simple answer alone with explaination.

tried to get the previous value of the null value but it shows 0 records using this query :

CREATE TABLE messages (
channelid INTEGER,
messageid INTEGER,
timesent DATETIME
);
INSERT INTO messages (channelid, messageid, timesent)
VALUES (10, 2, '2022-07-04 04:04');

INSERT INTO messages (channelid, messageid, timesent)
VALUES (10, 5, '2022-07-04 08:04');

INSERT INTO messages (channelid, messageid, timesent)
VALUES (10, 3, NULL);

INSERT INTO messages (channelid, messageid, timesent)
VALUES (10, 7, '2022-07-04 08:04');

`SELECT timesent 
 FROM messages 
 WHERE timesent < messages.timesent and not null `

SQL fiddle

CodePudding user response：

Supposing that the messageid column specifies the order of messages by which we can define what the previous value is, you can use a subquery as the following:

select channelid, messageid, 
  (
    select timesent from messages t2 
    where t2.channelid = t1.channelid and 
          t2.messageid <= t1.messageid and 
          t2.timesent is not null 
    order by t2.messageid desc limit 1
  ) timesent
from messages t1
order by messageid

Another approch, you can use the conditional max window function to define groups that connect the null values with the last not null value as the following:

select channelid, messageid, 
       coalesce(timesent, max(timesent) over (partition by channelid, grp)) as timesent
from
  (
    select *,
      max(case when timesent is not null then messageid end) over
         (partition by channelid order by messageid) as grp
    from messages 
  ) t
order by messageid

And if the timesent value will not be decreased with the increasing of the messageid value, then you can use the running max window function as the following:

select channelid, messageid, timesent_not_null as timesent
from
(
  select *, 
    max(timesent) over (partition by channelid order by messageid) timesent_not_null
  from messages
) t
-- where some condition (if needed)
order by messageid

See demo