1

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

-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

3
  • It may work, but the order of the rows in a table should not be trusted without an ORDER BY.
    – Rick James
    Jun 6 '19 at 3:43
  • The result is visually correct? It is random.
    – Akina
    Jun 6 '19 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 '19 at 5:53
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.