Get the Largest Count of Consecutive Value

Question

I have this table that records win and lose. Let us say this is my table

create table test(
  col1 int not null
)Engine=InnoDB;

it has 10 rows :

0,1,1,1,0,1,1,0,0,1

1=Win and 0=Lose. I want to get max count of the consecutive win and lose. For this example, the max count for consecutive win is 3 and for lose is 2. How can I get my desired result? I'm not sure where to start with this one. Thanks in advance.

EDIT

create table t1 (
 colid int not null auto_increment,
 col1 int,

 primary key(colid)
);

insert into t1(col1) values (0);
insert into t1(col1) values (1);
insert into t1(col1) values (1);
insert into t1(col1) values (1);
insert into t1(col1) values (0);
insert into t1(col1) values (1);
insert into t1(col1) values (1);  
insert into t1(col1) values (0);
insert into t1(col1) values (0);
insert into t1(col1) values (1);


select 
a.col1,
max(a.rn)
from (

        select 
        x.col1,
        if (@prev = col1 ,@current := @current + 1,@current := 1 ) rn, @prev := col1
      from t1 x , (select @prev := - 1 , @current := 1) v  
      order by x.colid
      ) a
group by a.col1

this one works the way I see the results.. Is there anything I should consider? I only have knowledge on basic joins. I haven't fully understand the query as of now..

Answer 1

create table t1 (
col1 int 
);

insert into t1 values (0);
insert into t1 values (1);
insert into t1 values (1);
insert into t1 values (1);
insert into t1 values (0);
insert into t1 values (1);
insert into t1 values (1);  
insert into t1 values (0);
insert into t1 values (0);
insert into t1 values (1);

select t.col1,
   max(t.rn)
  from (
select t1.col1,
if (@prev = col1 ,@current := @current + 1,@current := 1 ) rn,
   @prev := col1
 from t1,(select @prev := - 1 ,
              @current := 1) v  
 ) t
 group by t.col1;

DB fiddle

Answer 2

First of all, you can't trust that table to work. A table, by definition, is unordered. SQL is free to return the ones and zeros in any order.

So... You need to have another column that can be used to sort on -- a date or a game-number, or something.

Then you can ask the question because you can do a SELECT ... ORDER BY ....

The rest is messy. You should consider, instead, writing the algorithm in your favorite programming language other than SQL.

1. Add an AUTO_INCREMENT column and index it.
2. Do a self join on a.id = b.id-1 to find where the transitions are (0 to 1 or 1 to 0)
3. Extract only the transition ids.
4. Repeat the auto_in game, but instead of finding transitions, subtract the old ids. This gives you the run lengths.
5. SELECT ... ORDER BY .. LIMIT 1 to find the longest run, together with some of the history of where it came from.