CODEWITHSUNDEEP
oracle-database
Ângelo Rigo
Ângelo Rigo

Reputation: 2157

Oracle Get last know value if a column is NULL

Using Oracle I need to get the previous value just to fill a column .

I have sealing_id column and assay_id column.

I try :

LAG(sealing_id, 1) OVER (ORDER BY service_id DESC) AS sealing_id

but the query is too slow.

How can I just get the last know value to the sealing column if I have a NULL value ?

Upvotes: 1

Views: 85

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21075

Use LAST_VALUE analytic function. The IGNORE NULLS will keep the value in following NULL columns, but not in the first NULL column - see example.

create table x as 
select 0 x, NULL y from dual union all
select 1 x, 1 y from dual union all
select 2 x, NULL y from dual union all
select 3 x, 3 y from dual union all
select 4 x, NULL y from dual union all
select 5 x, NULL y from dual union all
select 6 x, 2 y from dual union all
select 7 x, NULL y from dual;

select x, y,
last_value(y ignore nulls) over (order by x) last_y 
from x order by x;

gives

        X          Y     LAST_Y
---------- ---------- ----------
         0                       
         1          1          1 
         2                     1 
         3          3          3 
         4                     3 
         5                     3 
         6          2          2 
         7                     2

And you are right, this query need sorting, it will return result a bit slower. But this sould be nearly comparable this a SELECT with ORDER BY on your ordering column.

Upvotes: 2

Related Questions