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 with the same description on multiple adjacent rows for >= 4 rows
and set tag = 'y' and order by id
(id,description, tag),
(1, 'xxx', 'n'),
(2, 'xxx', 'n'),
(3, 'xxx', 'n'),
(7, 'xxx', 'n'),
(5, 'xxx', 'n'),
(8, 'xxx', 'n'),
(4, 'xxx', 'n'),
(6, 'zzz', 'n')
desired result
(1, 'xxx', 'y')
(2, 'xxx', 'y')
(3, 'xxx', 'y')
(4, 'xxx', 'y')
(5, 'xxx', 'y')
This is called as gaps and island problem. Something like this should work
;with cte as
(SELECT id,
description,
tag = 'y' ,
cnt = Count(*)over(partition by description, grp)
FROM (SELECT *,
grp = Sum(CASE WHEN prev_description = description THEN 0 ELSE 1 END)Over(Order by id)
FROM (SELECT *,
prev_description = Lag(description) OVER(ORDER BY id)
FROM Yourtable) a) b
GROUP BY 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(partition by description, grp)
FROM (select Grp = row_number()over(order by id) -
row_number()over(partition by description order by id), *
from Yourtable) b
GROUP BY id, description, grp)
Select * from cte
Where cnt >= 4
I think this will do it
select *, 'y' as 'newTag'
from ( select *
, count(*) over (partition by [description], grp) as 'grpSize'
from ( select *
, ( [id] - row_number() over (partition by [description] order by [id]) ) as grp
from [consecutive]
) tt
) ttt
where grpSize >= 4
order by [description], grp, [id]
