SQL Server 2000 : generating and incrementing data from column conditionally without using CURSOR

Question

:)

Is there any way to create an index, and incrementing with a given condition, but without CURSOR handling/usage

For example:

The condition in my case is that: "if the current color (this is the item to be checked) is the same as the last one: not increment, otherwise increment in one unit"

This must be in a SQL query with no CURSOR USAGE and of course a good time (work with ... 10000 rows at least)

Thanks in advance.

EDIT: I forgot to mention that NEW_INDEX Column doesn't exist. It must be generated with the with the query.

EDIT2: Is there a way that only make use of SELECT/INSERT/UPDATE statements? (not set, declare...)

Answer 1

Assume a table called Colors with fields ID, Color, and ColorIndex of types int, varchar, and int respectively. I also assume the OP means prev / after based on an ordering of the ID field in asc order.

You could do this without a cursor, and use a while loop...but it definately isn't set based:

DECLARE @MyID int
DECLARE @CurrentIndex int
DECLARE @CurrentColor varchar(50)
DECLARE @PreviousColor varchar(50)

SET @CurrentIndex = (SELECT 0)

SET @MyID = (SELECT TOP 1 ID FROM Colors ORDER BY ID ASC)
SET @CurrentColor = (SELECT '')
SET @PreviousColor = (SELECT Color FROM Colors WHERE ID = @MyID)

WHILE (@MyID IS NOT NULL)
 BEGIN
   IF (@CurrentColor <> @PreviousColor)
     BEGIN
        SET @PreviousColor = (SELECT Color FROM Colors WHERE ID = @MyID)
        SET @CurrentIndex = (SELECT @CurrentIndex + 1)
        UPDATE Colors SET ColorIndex = @CurrentIndex WHERE ID = @MyID
     END 
   ELSE
      BEGIN
        UPDATE Colors SET ColorIndex = @CurrentIndex WHERE ID = @MyID
        SET @PreviousColor = (SELECT Color FROM Colors WHERE ID = @MyID)
      END
 SET @MyID = (SELECT TOP 1 ID FROM Colors WHERE ID > @MyID ORDER BY ID ASC)
 SET @CurrentColor = (SELECT Color FROM Colors WHERE ID = @MyID)
 END

The result after execution:

Performance wasn't too shabby as long as ID and color are indexed. The plus side is it is a bit faster then using a regular old CURSOR and it's not as evil. Solution supports SQL 2000, 2005, and 2008 (being that you are using SQL 2000 which did not support CTEs).

Answer 2

declare @ID int, 
        @MaxID int, 
        @NewIndex int, 
        @PrevCol varchar(50)

select @ID = min(ID),
       @MaxID = max(ID),
       @PrevCol = '',
       @NewIndex = 0       
from YourTable       
   
while @ID <= @MaxID
begin
  select @NewIndex = case when Colour = @PrevCol 
                       then @NewIndex 
                       else @NewIndex + 1 
                     end,
         @PrevCol = Colour
  from YourTable
  where ID = @ID
  
  update YourTable 
  set NewIndex = @NewIndex
  where ID = @ID
  
  set @ID = @ID + 1
end

https://data.stackexchange.com/stackoverflow/q/122958/