How to fill nulls with previous value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to fill nulls with previous value

Author

Topic

yaminmuyal
Starting Member

2 Posts

Posted - 2006-08-09 : 14:31:18

Hi,
I want to fill the nulls in a column with the value from the row just before it... That is, for example, if my table is

Col1
1
5
null
2
null
null
9

I need to output
Col1
1
5
5
2
2
2
9

Does anyone have an idea about how to do it?

Thanks a lot!!!
Yamin

Code Carpenter
Starting Member

4 Posts

Posted - 2006-08-09 : 14:47:07

Unless you have another column that is in use to make the results come back in a specific order, it would be very messy trying to get the first null to get a 5, and the next two to both get 2.

How are these fields ordered?

yaminmuyal
Starting Member

2 Posts

Posted - 2006-08-10 : 12:38:09

the fields aren't ordered at all...
But having a second ordered column was a nice idea!
Look, this worked:
set nocount on

declare @t table (col1 int)
declare @t1 table(num int IDENTITY(1,1) , col1 int)

insert into @t values(1)
insert into @t values(null)
insert into @t values(5)
insert into @t values(null)
insert into @t values(2)
insert into @t values(null)
insert into @t values(null)
insert into @t values(9)

insert into @t1 select * from @t

select * from @t1

select a.num, a.col1,
(select top 1 col1 from @t1 as b where not b.col1 is null and b.num <= a.num order by num desc)
from @t1 as a

thanks a lot!
Yamin

Code Carpenter
Starting Member

4 Posts

Posted - 2006-08-10 : 14:00:19

No problem. I am glad it worked.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-10 : 16:30:58

Yamin -- please consider using primary key constraints on your table ....

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources