How to query from a list like : [1,1,1,2,3,4,2,2,5,3]

Question

I want to query only numbers which are repeated 3 times consecutively like 1 here. But not 2 as it is not consecutive.

How can i achieve same in sql?

Answer 1

SQL table represent unordered sets (well, technically multisets). Assuming that your "list" is stored as rows in a table and there is an ordering column, then you can use lead():

select distinct number
from (select t.*,
             lead(number) over (order by <ordering col>) as next_number,
             lead(number, 2) over (order by <ordering col>) as next_number_2
      from t
     ) t
where number = next_number and number = next_number_2;

Answer 2

If the data is stored as rows in a table Gordon's logic can be simplified and modified for any number of repetitions:

select distinct number
from t
qualify
   -- same value in the last n rows, n=3
   min(number) over (order by <ordering col> rows 2 preceding) =
   max(number) over (order by <ordering col> rows 2 preceding)
   -- to avoid false positives for the first n-1 rows
and count(number) over (order by <ordering col> rows 2 preceding) = 3