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!

mySQL? Sql Server? oracle? Seems like recursive query may work but mysql doesn't support that; so we'd have to use user variables I think... So RDBMS product would help Grouping sets would/Could add the "7th" row. or a union back to a base set inquiry... — xQbert, Apr 06 '15 at 19:24
Also of interest http://stackoverflow.com/questions/17046204/how-to-find-the-boundaries-of-groups-of-contiguous-sequential-numbers/17046749#17046749 — Brad, Apr 06 '15 at 19:54

score 4 · Accepted Answer · answered Apr 06 '15 at 19:45

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.

score 2 · Answer 2 · answered Apr 06 '15 at 20:10

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

answered Apr 06 '15 at 20:10

Marcelo

429
1
6
19

Thanks for the idea but it does not give the correct results. it does not work when last row in the group is 1 for example. – Joy Walker Apr 07 '15 at 14:31

score 2 · Answer 3 · answered Apr 06 '15 at 20:38

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

more code but easier to understand for sure. very clever use of variables in update statement. It requires 3 table scans though. Thank you! — Joy Walker, Apr 07 '15 at 02:56
the other answers where before mine, I just wanted to say another way, maybe it has performance issue but it has useful idea inside, that's why I wrote the answer. You are welcome bro. — void, Apr 07 '15 at 09:25

how to get maximum consecutive row count using sql

3 Answers3