How to fetch records from a table where values are same for minimum three rows

Question

I have created following table:

id	val
1	0
2	1
3	0
4	1
5	1
6	1
7	0

Create Table Test3
(
    id int,
    val int
)

Insert Into Test3 Values (1,0),(2,1),(3,0),(4,1),(5,1),(6,1),(7,0)

I want output as

id
4
5
6

because consecutive three values are same in [val] column. And if the 7th row also has 1 the that id number should also reflect in output.

How can I solve this?

I have tried this with self join but not getting the expected output. Thank you in advance!

Answer 1

You can do this with LAG and LEAD:

SELECT id, val
  FROM (
SELECT *, CASE WHEN (Val = LAG(Val,1) OVER (ORDER BY ID) AND Val = LAG(Val,2) OVER (ORDER BY ID)) 
                 OR (Val = LEAD(Val,1) OVER (ORDER BY ID) AND Val = LEAD(Val,2) OVER (ORDER BY ID)) 
                 OR (Val = LEAD(Val,1) OVER (ORDER BY ID) AND Val = LAG(Val,1) OVER (ORDER BY ID)) 
                 THEN 1 ELSE 0 END AS Cnte
  FROM Test3
       ) a
 WHERE Cnte >= 1;

ID	Val
4	1
5	1
6	1

If you extend the example data:

VALUES (1,0),(2,1),(3,0),(4,1),(5,1),(6,1),(7,0),(8,3),(9,3),(10,3),(11,10),(12,4),(13,4),(15,4),(16,4)

ID	Val
4	1
5	1
6	1
8	3
9	3
10	3
12	4
13	4
15	4
16	4