I have a table with 2 columns:

• val with values: 0 or 1
• id with unique identifiers

select 0 val, 0 id into #tmp union all 
select 1, 1   union all 
select 1, 2   union all 
select 0, 3   union all 
select 1, 4  union all 
select 1, 5   union all 
select 1, 6   union all 
select 1, 7   union all 
select 1, 8   union all 
select 1, 9   union all 
select 1, 10

How do I to find id with 6 values = 1 in a row.

In the example above: id = 9, id = 10.

It is desirable not to use loops (cursors or while), but something like sum() over.

CodePudding user response：

Why not LAG() (but you need an order column):

SELECT id
FROM (
   SELECT 
      id,
      val,
      val1 = LAG(val, 1) OVER (ORDER BY id),
      val2 = LAG(val, 2) OVER (ORDER BY id),
      val3 = LAG(val, 3) OVER (ORDER BY id),
      val4 = LAG(val, 4) OVER (ORDER BY id),
      val5 = LAG(val, 5) OVER (ORDER BY id)
   FROM YourTable
) t
WHERE val = 1 AND val1 = 1 AND val2 = 1 AND val3 = 1 AND val4 = 1 AND val5 = 1

CodePudding user response：

Another approach is using ROW_NUMBER on the LAG values

declare @tmp table (val int, id int)
insert into @tmp 

select 0, 0 union all 
select 1, 1 union all 
select 1, 2 union all 
select 0, 3 union all 
select 1, 4 union all 
select 1, 5 union all 
select 1, 6 union all 
select 1, 7 union all 
select 1, 8 union all 
select 1, 9 union all 
select 1, 10

select t2.id,
       t2.islandcount
from   ( select t.id,
                t.val,
                t.priorval,
                row_number() over (partition by t.val, t.priorval order by t.id) as islandcount
         from   ( select id,
                         val,
                         lag(val, 1) over (order by id) priorval
                  from   @tmp
                ) t
       ) t2 
where  t2.islandcount >= 6

the result is

id  islandcount
9   6
10  7

Try it yourself in this DBFiddle

The advantage of this method is that you can easy set the value from 6 to any other value