# 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..

``````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

• It may work, but the order of the rows in a table should not be trusted without an `ORDER BY`. Jun 6, 2019 at 3:43
• The result is visually correct? It is random. Jun 6, 2019 at 5:03
• not sure why this one got down voted.. this one works.. i even added and auto-increment column and an order by.. I don't have 15 reputation to accept it as answer.. i will be editing my question
– Ago
Jun 6, 2019 at 5:53

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.