how to get maximum consecutive row count using sql

Question

I have a sql puzzle and can't figure out a proper way to do it except brute force case statement. Hope there are good ideas how to achieve this. Thanks for your thoughts. I am on sql server 2012.

basically, I have groups of rows and each group has fixed 6 rows with value of either 1 or 0. Now, I need to insert a new row after each group and fill in the maximum consecutive row count for that group. see below:

group_name, row_number, yes_no
A, 1, 1
A, 2, 0
A, 3, 1
A, 4, 1
A, 5, 1
A, 6, 0
B, 1, 1
B, 2, 1
B, 3, 0
B, 4, 1
B, 5, 0
B, 6, 0

Now I would like the results to be:

group_name, row_number, yes_no
A, 1, 1
A, 2, 0
A, 3, 1
A, 4, 1
A, 5, 1
A, 6, 0
**A, 7, 3**
B, 1, 1
B, 2, 1
B, 3, 0
B, 4, 1
B, 5, 0
B, 6, 0
**B, 7, 2**

notice row_number 7 is a new row with the number of max consecutive rows of 1. any idea how to do this? Thank you!

Answer 1

I want to provide another solution, using variables + a table variable or a temp table; copy data from original table into declared table variable then update the declared table by updating yes_no field per each row, then insert into original table by selecting max values from declared table:

assuming your original table is:

create table tbl(group_name varchar(10),
                 row_number int, 
                 yes_no int);

and your data is:

insert into tbl values
('A', 1, 1),
('A', 2, 0),
('A', 3, 1),
('A', 4, 1),
('A', 5, 1),
('A', 6, 0),
('B', 1, 1),
('B', 2, 1),
('B', 3, 0),
('B', 4, 1),
('B', 5, 0),
('B', 6, 0);

now you can do:

declare @tbl table(group_name varchar(10),
                 row_number int, 
                 yes_no int);

insert into @tbl
select * from tbl order by group_name,row_number;


declare @grp varchar(1)
declare @rn int
declare @yn int
set @yn=0

update @tbl
set 
@grp=group_name,
@yn= case 
      when (@grp=group_name and yes_no=0) then 0
      when (@grp=group_name and yes_no!=0) then @yn+1
      else 0
     end,

yes_no= case 
         when yes_no=1 then @yn
         else yes_no 
        end;


insert into tbl
select group_name,max(row_number)+1,max(yes_no) 
from @tbl 
group by group_name

and check:

select * from tbl
order by 1,2

result:

group_name  row_number  yes_no
A   1   1
A   2   0
A   3   1
A   4   1
A   5   1
A   6   0
A   7   3 --new row exactly as you want
B   1   1
B   2   1
B   3   0
B   4   1
B   5   0
B   6   0
B   7   2 --new row exactly as you want

Answer 2

I hope this help. I just take the greatest distance between 0's grouping by group_name

INSERT INTO yourtable 
(group_name, row_number, yes_no)
SELECT 
    t1.group_name, 
    7 AS row_number,
    MAX(t2.row_number - t1.rownumber) - 1 as yes_no
FROM yourtable t1
INNER JOIN yourtable t2
ON  t1.group_name = t2.group_name AND 
    t1.row_number < t2.row_number AND  
    t1.yes_no = 0 AND 
    t1.yes_no = t2.yes_no
GROUP BY t2.group_name

Answer 3

You can get the maximum consecutive row count by subtracting a sequence number from row_number and assigning a group. For instance, the following gets information about all consecutive values in the data:

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

To get what you want, you need an insert and one more level of aggregation -- to get the maximum count:

insert into table(group_name, row_number, yes_no)
    select group_name, maxrn + 1, max(cnt)
    from (select group_name, yes_no, count(*) as cnt, max(row_number) as maxrn
          from (select t.*,
                       (row_number - row_number() over (partition by group_name, yes_no
                                                        order by row_number)
                       ) as grp
                from table t
               ) t
          group by group_name, grp, yes_no
         ) t
    group by group_name
   ) t

Note: your question is unclear on whether you want the longest group of 1s and 0s or just 1s. This gets both. If you want just 1s, you can insert a where clause before the last group by.