Get the Largest Count of Consecutive Value 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..
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.


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