Fill in rows to be 1+ the number in previous row 2

[bmacbmac]

Hello,

I have the following data in mytable


ROWID BOOKNO PAGENO DOCCOUNTER
1 3000 1 1
2 3000 2 NULL
3 3000 3 1
4 3000 4 NULL
5 3000 5 NULL
6 3000 6 1
7 3000 7 1
8 3000 8 1
9 3000 9 1
10 3000 10 NULL
11 3000 11 NULL
12 3000 12 NULL
13 3000 13 NULL
14 3000 14 1
15 3000 15 NULL
16 3000 16 NULL
17 3000 17 NULL
18 3000 18 1
19 3000 19 NULL
20 3000 20 NULL

Is there a way I can can fill in the DOCCOUNTER column with 1+ the number above it? So basically starting over at 1 each time. To be something like this:

ROWID BOOKNO PAGENO DOCCOUNTER
1 3000 1 1
2 3000 2 2
3 3000 3 1
4 3000 4 2
5 3000 5 3
6 3000 6 1
7 3000 7 1
8 3000 8 1
9 3000 9 1
10 3000 10 2
11 3000 11 3
12 3000 12 4
13 3000 13 5
14 3000 14 1
15 3000 15 2
16 3000 16 3
17 3000 17 4
18 3000 18 1
19 3000 19 2
20 3000 20 3

Thanks in advance for any ideas!

 

[danvlas]

If SQL 2005 or higher, look up Row_number() function in BOL

SELECT RowID, BookNo, PageNo, ROW_NUMBER() OVER(PARTITION BY BookNo, PageNo ORDER BY RowID) AS 'DocCounter'
FROM MyTable


Daniel Vlas
Systems Consultant

 

[bmacbmac]

Thanks Danvlas. I always seem to forget to mention that I am using SQL 2000.

 

[bmacbmac]

Wow I am really stuck on this one. I guess I'll try the dreaded cursor.. ehhhhh. I pretty much suck at cursors, can you take a look at this? I'm not sure how to get the cursor to assist in updating my table?

SET NOCOUNT ON
GO

DECLARE EmptyPage SCROLL CURSOR
FOR SELECT bookno, booknosort, pageno, pagenosort,doccounter    FROM #temp7 
order by booknosort, pagenosort

DECLARE @bookno varchar (25)
DECLARE @Booknosort varchar (25)
DECLARE @doccounter Int
DECLARE @pageno varchar (25)
DECLARE @pagenosort varchar (25)
DECLARE @bookno1 varchar (25)
DECLARE @Booknosort1 varchar (25)
DECLARE @doccounter1 Int
DECLARE @pageno1 varchar (25)
DECLARE @pagenosort1 varchar (25)
DECLARE @DocNext Int


OPEN EmptyPage

FETCH RELATIVE 2 From DocCount INTO @bookno, @booknosort, @pageno, @pagenosort, @doccounter --@sdocumentnosort--, @PageNumTo
SELECT @DocNext = @doccounter
FETCH RELATIVE -1 FROM DocCount INTO @bookno1, @booknosort1, @pageno1, @pagenosort1, @doccounter1

   WHILE (@@FETCH_STATUS = 0)
      BEGIN
         IF @DocNext IS NULL
--THIS IS WHERE I AM STUCK.  HOW DO I USE CURSOR TO UPDATE ---MY TABLE? 
        BEGIN update #temp7 set doccounter = @doccounter1 + 1
		from EmptyPage
		join #temp7 on #temp7.bookno = @bookno1 and #temp7.pageno = @pageno
	    END
	 FETCH RELATIVE 2 From DocCount INTO @bookno, @booknosort, @pageno, @pagenosort, @doccounter--, @PageNumTo
            IF (@@FETCH_STATUS = -1) BREAK
         SELECT @DocNext = @doccounter
         FETCH RELATIVE -1 FROM DocCount INTO @bookno1, @booknosort1, @pageno1, @pagenosort1, @doccounter1--, @PageNumTo

		  
      END

DEALLOCATE EmptyPage

 

[Crowley16]

have you got a clustered index on the rowid column?

you could try something like:

update table
set
@Counter = DOCCOUNTER
, DOCCOUNTER = isnull(DOCCOUNTER, 0) + @Counter

not sure if this will work, don't have an instance of 2k to test with, so use with care...


--------------------
Procrastinate Now!

 

[gmmastros]

Is the RowId Column guaranteed to be incrementing by one without any gaps?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bmacbmac]

Thanks for the replies everyone...

Crowley16 - I honestly don't know if the rowid column is clustered. Is your update example to use independently or within the cursor? Independently I wants me to declare @counter. In the cursor, it doesn't update the NULL values in my table. In fact, it changed all of my 1 values in doccounter to 2048. I'll keep playing with it.

gmmastros - I am doing a new insert into a #temp table with the rowid as an identity. So, yes there will be consistent with no gaps.

 

[bmacbmac]

Would it be more simple if I updated only the NULL value directly below a non NULL value? Then run it again to get the next NULLs, and again, and again, etc? I would really only have to run it 9 or 10 times as I don't the gaps will be larger than that.

Thanks!

 

[RyanEK]

Hi,

Try the following

declare @tmp table (rowid int, bookno int, pageno int, docounter int)
insert into @tmp values (1, 3000, 1, 1)
insert into @tmp values (2, 3000, 2, null)
insert into @tmp values (3, 3000, 3, 1)
insert into @tmp values (4, 3000, 4, null)
insert into @tmp values (5, 3000, 5, null)
insert into @tmp values (6, 3000, 6, 1)
insert into @tmp values (7, 3000, 7, 1)
insert into @tmp values (8, 3000, 8, 1)
insert into @tmp values (9, 3000, 9, 1)
insert into @tmp values (10, 3000, 10, null)

declare @nullcnt int
set @nullcnt = 1
WHILE (@nullcnt > 0)
begin 
 update u
  set u.docounter = (select top 1 docounter 
                     from @tmp t2 where t2.pageno < u.pageno
                     order by pageno desc) + 1
 from @tmp u
 where docounter is null
 
 select @nullcnt = count(rowid) from @tmp where docounter is null
end

select * from @tmp

 

[bmacbmac]

Brilliant! Thanks RyanEK. That was a lifesaver!

 

[bmacbmac]

Hello. Bringing this one back to life hoping RyanEK or someone can help.

I just found out I'll have more than one book to run this against. More like 3000 books. This adds a new element to my issue. Consider the following with more than one book:

declare @tmp table (rowid int, bookno int, pageno int, docounter int)
insert into @tmp values (1, 3000, 1, 1)
insert into @tmp values (2, 3000, 2, null)
insert into @tmp values (3, 3000, 3, null)
insert into @tmp values (4, 3000, 4, null)
insert into @tmp values (5, 3000, 5, null)
insert into @tmp values (6, 3000, 6, 1)
insert into @tmp values (7, 3000, 7, 1)
insert into @tmp values (8, 3000, 8, 1)
insert into @tmp values (9, 3000, 9, 1)
insert into @tmp values (10, 3001, 10, null)
insert into @tmp values (11, 3001, 1, 1)
insert into @tmp values (12, 3001, 2, null)
insert into @tmp values (13, 3001, 3, 1)
insert into @tmp values (14, 3001, 4, null)
insert into @tmp values (15, 3001, 5, null)
insert into @tmp values (16, 3001, 6, 1)
insert into @tmp values (17, 3001, 7, 1)
insert into @tmp values (18, 3001, 8, 1)
insert into @tmp values (19, 3001, 9, 1)
insert into @tmp values (20, 3001, 10, null)

declare @nullcnt int
set @nullcnt = 1
WHILE (@nullcnt > 0)
begin 
 update u
  set u.docounter = (select top 1 docounter 
                     from @tmp t2 where t2.pageno < u.pageno
                     order by pageno desc) + 1
 from @tmp u
 where docounter is null
 
 select @nullcnt = count(rowid) from @tmp where docounter is null
end

select * from @tmp

The docounter eventually starts skipping numbers. Look at the results around bookno 3001 pageno 4.

I tried changing the 'order by' in the update script to order by bookno, pageno but that didn't help. I also changed it to order by rowid, but that didn't help either. Can you think of an adjustment I can make for multiple books?

Thanks!

 

[RyanEK]

Hi,

If you can guarantee rowid as being sequentially in the right order try the following:

declare @nullcnt int
set @nullcnt = 1
while (@nullcnt > 0)
begin  
  update u  
  set u.docounter = (select top 1 docounter
					 from @tmp t2 where t2.rowid < u.rowid                     
					 order by rowid desc) + 1 
  from @tmp u 
  where docounter is null  
	
  select @nullcnt = count(rowid) from @tmp where docounter is null
end

 

[bmacbmac]

Thanks RyanEK. That works great!