Strange Loop Behavior 2

[briangriffin]

A variable table holds rownum, which is an identity column, distdate, and an empty ranking column. I'm trying to update the ranking column with the rownum, but resetting it at each change of distdate:

RowNum Distdate Ranking
1 7/26/2010 1
.......
199 7/26/2010 199
200 7/27/2010 1
.....
425 7/30/2010 1
500 7/30/2010 76


First thing I've done is set the ranking = rownum for the min distdate:

update @v_table1
set ranking = rownum where distdate = (select min(distdate) from @v_table1)

Then I update the next lowest distdate with the rownum minus the highest rownum of the previous date:

update @v_table1
set ranking = rownum - (select max(rownum) from @v_table1 where distdate = (select max(distdate) from @v_table1 where ranking is not null))
where distdate = (select min(distdate) from @v_table1 where ranking is null)

The initial specs were to do this for one week, so I just copied the above code and pasted it in five times. This works fine for the six distinct dates in the table.

However, now it may be run for a longer date range, so I want to turn it into a loop. This is what I have:

declare 
@v_counter int,

set @v_counter = 1
while @v_counter < (select count(distinct distdate) from @v_table1 where ranking is not null)  -- Returns 5

BEGIN
update @v_table1
set ranking = rownum - (select max(rownum) from @v_table1 where distdate = (select min(distdate) from @v_table1 where ranking is not null))
where distdate = (select min(distdate) from @v_table1 where ranking is null)
set @v_counter = @v_counter + 1
END

However, the loop is not updating as expected - it is updating the second lowest date starting with 1, but goes through the last record of the table without resetting.

I know the loop is executing the proper number of times, but why is the behavior of the update different in the loop than when I don't use a loop but instead paste the same code in multiple times?

If I change the code to "... while @v_counter < 2..." then it updates the second date properly, and the ranking of all subsequent dates remains null.

This has to be 2000 compatible, so I don't think there are any ranking functions available to me (but I could be wrong).

Thanks in advance.

 

[SQLBill]

One issue I see is:

while @v_counter < (select count(distinct distdate) from @v_table1 where ranking is not null)  -- Returns 5

That will never do the last row. It is WHILE the counter is LESS than the number of rows. In this case it means WHILE the counter is less than 5. Therefore, it will update rows 1, 2, 3, & 4. You can fix this by either using <= or changing it to this:

while @v_counter < (select count(distinct distdate) + 1 from @v_table1 where ranking is not null)

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[briangriffin]

Thanks, SQLBill - I'm not surprised I missed that, since I've reworked it a dozen ways to get it going. Any idea why the loop is essentially ignoring the Where clause?

 

[PDreyer]

update @v_table1
set ranking=a.rownum-b.minrownum+1
from @v_table1 a
join 
(select minrownum=min(rownum),distdate 
 from @v_table1 
 group by distdate
) b
  on a.distdate=b.distdate

 

[briangriffin]

Beautiful, thank you! Saved that code in my 'Handy Code' file.

 

[Emtucifor]

If you have SQL 2005 and up:

with data as (
   select *, newranking = rownum - min(rownum) over (partition by distdate) + 1
   from @v_table1
)
update data set ranking = newranking

 

[Emtucifor]

And in case I misunderstood your requirements:

with data as (
select *, newranking = row_number() over (partition by distdate order by rownum)
from @v_table1
)
update data set ranking = newranking