Search notes:

Oracle SQL: Fill missing values (gaps) with previous value

create table tq84_tab (
   nm  number,
   tx  varchar2(5)
);

insert into tq84_tab values ( 1,  null);
insert into tq84_tab values ( 2, 'abc');
insert into tq84_tab values ( 3,  null);
insert into tq84_tab values ( 4,  null);
insert into tq84_tab values ( 5, 'def');
insert into tq84_tab values ( 6,  null);
insert into tq84_tab values ( 7,  null);
insert into tq84_tab values ( 8,  null);
insert into tq84_tab values ( 9, 'ghi');
insert into tq84_tab values (10,  null);

select
   nm,
   tx,
   nvl (                   -- If current value of tx
      tx,                  -- is not null, then evaluate to tx,
      lag(tx)              -- otherwise, evaluate to preceding
        ignore nulls       -- non-null value of tx.
        over (order by nm) -- order by clause required to
                           -- determine «preceding»
   ) as tx_
from
   tq84_tab;
--
--    NM TX    TX_ 
-- ----- ----- -----
--     1           
--     2 abc   abc 
--     3       abc 
--     4       abc 
--     5 def   def 
--     6       def 
--     7       def 
--     8       def 
--     9 ghi   ghi 
--    10       ghi 

drop   table tq84_tab;

See also

The analytic functions LEAD and LAG.

Index

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1759419851, '216.73.216.42', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/SQL/select/analytic/lag-lead/fill-missing-values-with-previous-value/index(77): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78