LAG functions and NULLS

How can I tell the LAG function to get the last “not null” value?

For example, see my table bellow where I have a few NULL values on column B and C. I’d like to fill the nulls with the last non-null value. I tried to do that by using the LAG function, like so:

case when B is null then lag (B) over (order by idx) else B end as B,

but that doesn’t quite work when I have two or more nulls in a row (see the NULL value on column C row 3 – I’d like it to be 0.50 as the original).

Any idea how can I achieve that? (it doesn’t have to be using the LAG function, any other ideas are welcome)

A few assumptions:

The number of rows is dynamic;
The first value will always be non-null;
Once I have a NULL, is NULL all up to the end – so I want to fill it with the latest value.

Thanks

Answer

if it is null all the way up to the end then can take a short cut

declare @b varchar(20) = (select top 1 b from table where b is not null order by id desc);
declare @c varchar(20) = (select top 1 c from table where c is not null order by id desc); 
select is, isnull(b,@b) as b, insull(c,@c) as c 
from table;

Advertisement

Answer