filling in values from previous row

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)

filling in values from previous row

Author

Topic

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2006-12-17 : 10:39:45

I have a column with certain rows randomly containing a number other than zero. All zero values I want to be reset to the last nonzero value:

I am trying to update a column so colA will eventually look like colB
colA////colB
1 //// 1
0 //// 1
0 //// 1
2 //// 2
0 //// 2
9 //// 9
Any help would be appreciated.
Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-17 : 13:20:57

How about this?

update t1
set Col = (select max(col) from tbl t2 where t2.[ID] <= t1.[ID] and t2.Col <> 0)
from tbl t1

Note: I assume you have ID column which is identity column or some other primary key on the table.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2006-12-18 : 11:08:50

Hey, that worked great!

thanks

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2006-12-18 : 11:39:00

Sorry, it actually only works if the sporadic numbers are ascending, it's descending then obviously it grabs the MAX (col). Is their someway to incorporate the id, like a MIN or something?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-18 : 12:00:29

[code]-- prepare test data
declare @t table (rowid int identity(1, 1), colA int, colB int, peso int)

insert @t (colA, colB)
select 1, 1 union all
select 0, 1 union all
select 0, 1 union all
select 9, 9 union all
select 0, 2 union all
select 2, 2

-- do the work
update t1
set t1.peso = (select t3.colA from @t t3 where t3.rowid = (select max(t2.rowid) from @t t2 where t2.rowid <= t1.rowid and t2.colA <> 0))
from @t t1

select * from @t[/code]

Peter Larsson
Helsingborg, Sweden

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2006-12-19 : 14:24:14

thank-you!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 14:30:32

This might perform a little better

-- prepare test data
declare @t table (rowid int identity(1, 1), colA int, colB int, peso int)

insert	@t (colA, colB)
select	1, 1 union all
select	0, 1 union all
select	0, 1 union all
select	9, 9 union all
select	0, 2 union all
select	2, 2

-- do the work
update		t1
set		t1.peso = (select top 1 t2.colA from @t t2 where t2.rowid <= t1.rowid and t2.cola <> 0 order by t2.rowid desc)
from		@t t1

select * from @t

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources