Create index in a whole loop

0

Please login or register to vote for this query.

(click on this box to dismiss)

Enter Query Description

SharePoint

Q&A for SharePoint enthusiasts

-- Enter Query Title
-- Enter Query Description

declare @T table 
(
  ID int identity primary key,
  Colour varchar(10),
  NewIndex int
)

insert into @T(Colour) values ('RED')
insert into @T(Colour) values ('RED')
insert into @T(Colour) values ('BLUE')
insert into @T(Colour) values ('RED')
insert into @T(Colour) values ('RED')

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

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


select *
from @T

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results