Output the rows which are occurring more than two times consecutively

September 22, 2023

I have the below dataset of logs table where "ID" column is identity and "num" column represents a simple number and can have duplicates. I want to output only the values from the "num" column that occur more than two times consecutively. For example: value 1 occurs three times consecutively so the output has 1 as a result of first 3 rows. But values 1 and 2 neither repeated more than 2 times consecutively so they are not in the output:

Logs table:
+----+-----+
| id | num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+

Output: 
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+

I have tried the below query, but it returns the number of 1 as four times and not able to detect "consecutive" nature as required for the output:

with cte as(
 select logno,grp = sum(case when logno = repeat_val then 0 else 1 end) over (order by sno) from
 (
 select sno,logno,
 lag(logno) over(order by logno) as repeat_val
 from 
 logprac l) s)

 select logno as ConsecutiveNums , count(*) 
 from cte
 group by grp,logno
having count(*)>2
 order by grp;

>Solution :

This is a classic Gaps-and-Islands problem

Example

Select num
 From  (
        Select *
              ,Grp = row_number() over (order by id)
                    -row_number() over (partition by num order by id)
         From  YourTable
       ) A
 Group by Grp,num
 Having count(*)>2

Results