How To Tag A Group Of Repeating Items If The Ids Are Consecutive For N Rows?

Building in the test for consecutive ids is proving difficult without breaking it down into parts or using a cursor which I'd like to avoid. pseudo query - SELECT all FROM table w

Solution 1:

This is called as gaps and island problem. Something like this should work

;with cte as
(SELECT id, 
       description, 
       tag ='y' ,
       cnt =Count(*)over(partitionby description, grp)
FROM  (SELECT*, 
              grp =Sum(CASEWHEN prev_description = description THEN0ELSE1END)Over(Orderby id)
       FROM   (SELECT*, 
                      prev_description =Lag(description) OVER(ORDERBY id) 
               FROM   Yourtable) a) b 
GROUPBY id, description, grp 
)
Select*from cte 
Where cnt >=4

Another approach using Row_Number

;with cte as
(SELECT id, 
       description, 
       tag ='y' ,
       cnt =Count(*)over(partitionby description, grp)
FROM  (select Grp =row_number()over(orderby id) -row_number()over(partitionby description orderby id), *from Yourtable) b 
GROUPBY id, description, grp)
Select*from cte 
Where cnt >=4

Solution 2:

I think this will do it

select*, 'y'as'newTag'from ( select*
           , count(*) over (partitionby [description], grp) as'grpSize'from ( select* 
                   , ( [id] -row_number() over (partitionby [description] orderby [id]) ) as grp
              from [consecutive] 
            ) tt
     ) ttt 
where grpSize >=4orderby [description], grp, [id]