Fill empty row with 1+ data from row above

[bmacbmac]

Hello,

I have the following table / values:

create table #temp (counter int, imageid int, newpage int)
insert into #temp values (1,400, 1)
insert into #temp values (2,400, NULL)
insert into #temp values (3,400, NULL)
insert into #temp values (4,400, NULL)
insert into #temp values (5,400, NULL)
insert into #temp values (6,401, 1)
insert into #temp values (7,401, NULL)
insert into #temp values (8,401, NULL)
insert into #temp values (9,402, 1)
insert into #temp values (10,403, 1)
insert into #temp values (11,403, NULL)
insert into #temp values (12,403, NULL)
insert into #temp values (13,403, NULL)

Each imageid should have numerical pages starting at 1. I need to figure out a way to fill in the NULL values with 1+ the row above for EACH imageid. I'd like to end up with:
1,400,1
2,400,2
3,400,3
4,400,4
5,400,5
6,401,1
7,401,2
8,401,3
9,402,1
10,403,1
11,403,2
12,403,3
13,403,4


The data should be in order of the counter.
Each imageid starts with page 1.

This is SQL 2008.

Thanks so much for anyone's help!

 

[Olaf Doschke]

If this is only about such #temp data preparation with inserts, you can declare @newpage as int and increent it between inserts, resetting to 1 for new imageid

To give an example of partition usage, if you install AdventureWorks sample data, you could test this query:

Use [AdventureWorks2017];
SELECT Top 20 ROW_NUMBER() OVER (PARTITION BY SOD.[SalesOrderID] ORDER BY SOD.[SalesOrderDetailID]) as OrderDetailNo, 
SOH.*, SOD.* 
From [Sales].[SalesOrderHeader] SOH 
left join [Sales].[SalesOrderDetail] SOD on SOD.[SalesOrderID] = SOH.[SalesOrderID]
order by SOH.[SalesOrderID],  SOD.[SalesOrderDetailID]

In your case you could do:

create table #temp (counter int, imageid int)
insert into #temp values (1,400)
insert into #temp values (2,400)
insert into #temp values (3,400)
insert into #temp values (4,400)
insert into #temp values (5,400)
insert into #temp values (6,401)
insert into #temp values (7,401)
insert into #temp values (8,401)
insert into #temp values (9,402)
insert into #temp values (10,403)
insert into #temp values (11,403)
insert into #temp values (12,403)
insert into #temp values (13,403) 

create table #temp2 (counter int, imageid int, newpage int)
insert into #temp2
SELECT *, ROW_NUMBER() OVER (PARTITION BY imageid ORDER BY counter) as newpage
From #temp
ORDER BY counter 

Select * from #temp2

I bet your creation of temp data isn't really like that, the Adventureworks query shows how to create such a result from scratch in one go without a first temp table.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[johnherman]

This can be done with a CURSOR.
initialize imageid_old to -1.
The CURSOR would be built as SELECT counter, imageid, newpage FROM #temp.

Then FETCH (cursor_name) into counter_cur, imageid_cur, newpage_cur.
IF imageid_cur = imageid_old THEN
newpage_new = newpage_hold + 1
newpage_hold = newpage_new
ELSE
newpage_hold = 1
newpage_new = 1
ENDIF

UPDATE #temp SET image_id = image_cur, newpage = newpage_new
WHERE imageid = imageid_cur;

commit where appropriate, either here or at EOJ
move to next row in cursor.


==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity