8

I have a table like so

ID     OrdID     Value
1      1          0     
2      2          0
3      1          1
4      2          1
5      1          1
6      2          0
7      1          0
8      2          0
9      2          1
10     1          0
11     2          0

I want to get the count of consecutive value where the value is 0. Using the example above the result will be 3 (Rows 6, 7 and 8). I am using sql server 2008 r2.

Alex K
  • 8,269
  • 9
  • 39
  • 57
abeuwe
  • 159
  • 2
  • 15

2 Answers2

10

I am going to presume that id is unique and increasing. You can get counts of consecutive values by using the different of row numbers. The following counts all sequences:

select grp, value, min(id), max(id), count(*) as cnt
from (select t.*,
             (row_number() over (order by id) - row_number() over (partition by value order by id)
             ) as grp
      from table t
     ) t
group by grp, value;

If you want the longest sequence of 0s:

select top 1 grp, value, min(id), max(id), count(*) as cnt
from (select t.*,
             (row_number() over (order by id) - row_number() over (partition by value order by id)
             ) as grp
      from table t
     ) t
group by grp, value
having value = 0
order by count(*) desc
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    nice. also a LAG function should work.. similar to these window functions. – Randy Dec 02 '14 at 15:43
  • Hi you are correct, the ID is increasing (primary key). When I try your script in sql fiddle I get the following error. Incorrect syntax near the keyword 'table'.: select grp, value, min(id), max(id), count(*) as cnt from (select test.*, (row_number() over (order by id) - row_number() over (partition by value order by id) ) as grp from table test ) test group by grp, value – abeuwe Dec 02 '14 at 16:07
  • Okay there is a extra keyword Table after obtaining the value of grp. If you remove that it seems to work brilliantly. Thank you Gordon Linoff. – abeuwe Dec 02 '14 at 16:50
  • @abeuwe . . . `table` is just meant to be the name of your table. – Gordon Linoff Dec 02 '14 at 16:51
4

A query using not exists to find consecutive 0s

select top 1 min(t2.id), max(t2.id), count(*)
from mytable t
join mytable t2 on t2.id <= t.id
where not exists (
    select 1 from mytable t3
    where t3.id between t2.id and t.id
    and t3.value <> 0
)
group by t.id
order by count(*) desc

http://sqlfiddle.com/#!3/52989/3

FuzzyTree
  • 32,014
  • 3
  • 54
  • 85