Cursor question

[CAFCrew]

Hi,

I am trying to add a column to a table of selected data that steps up in order i.e. the first line is 1 the second line is 2 the third line is 3 etc where the ID of the one of the other columns is the same. When the ID changes i want to reset the counter to 1 again and start again

I have tried for ages to try and build this into a cursor but with no luck. I dont suppose anyone has got anything that they have written before that could help have they?

Many thanks in advance for your help

Matt

 

[mrdenny]

I don't have anything handy but the basic idea is that in addition to the variables for the current record you need to also keep track of the value of the ID field for the previous record. When they no longer match you need to reset back to 1.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[CAFCrew]

HI,

Thanks for this. This was the approach that i was trying for before before my post, but i have been trying for ages..its driving me mad!! i cant get the text in the loop right and how / where to set the variables. I am getting really fed up with it!!

Thanks for your help though

Matt

 

[bborissov]

What version of SQL Server? Becuase in SQL Server 2005 you have ranking functions.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[CAFCrew]

really...that would be handy. Its only SQL 2000 i am afraid. My company seem reluctant to upgrade for some reason!

Thanks tho

Matt

 

[mrdenny]

Untested code

declare @LastID int
declare @ID int, @PK int
declare @SmallID int
declare cur CURSOR for select PK, ID from table
open cur
fetch next from cur into @PK, @ID
set @LastID = -1
while @@FETCH_STATUS = 1
BEGIN
    if @ID <> @LastID
    begin
       set @LastID = @ID
       set @SmallID = 1
    end
    else
       set @SmallID = @SmallID + 1

    update table
    set SmallID = @SmallID
    where PK = @PK

    fetch next from cur into @PK, @ID
END
close cur
deallocate cur

This assumes a table named "table" and that you have a primary key of PK the master ID that you already have is called "ID" and the new field is called "SmallID".

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[CAFCrew]

wow......thank you sooooo much for this. Your a total star

I cant tell you how much i appreciate this....it was driving me mad

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com