MySQL sequence create -

i need pulling records happen in sequence in mysql environment.

my dataset consists of cross-country games , winning , losing country. need identify countries have won atleast 3 games in row. below reproducible example. created matches dataset.

    create table matches (date date, winner char(10), loser char(10));     insert matches (date,winner,loser) values (str_to_date('3-03-2013', '%m-%d-%y') ,'usa','china');     insert matches (date,winner,loser) values (str_to_date('3-05-2013', '%m-%d-%y') ,'usa','russia');     insert matches (date,winner,loser) values (str_to_date('3-06-2013', '%m-%d-%y') ,'france','germany');     insert matches (date,winner,loser) values (str_to_date('3-09-2013', '%m-%d-%y') ,'usa','russia');     insert matches (date,winner,loser) values (str_to_date('3-11-2013', '%m-%d-%y') ,'usa','india');     insert matches (date,winner,loser) values (str_to_date('3-15-2013', '%m-%d-%y') ,'usa','australia');     insert matches (date,winner,loser) values (str_to_date('3-15-2013', '%m-%d-%y') ,'usa','new zealand'); 

i created dataset has row number each country ordered date.

    create table matches2      (          date date,           winner char(10),          loser char(10),          row int      );     insert matches2      (         row,         winner,         date,         loser     )     select row,            winner,            date ,             loser          (        select winner,                (@winner:=@winner+1) row,                 date ,                loser        matches ,        (select @winner := 0) r     ) x     order date; 

the table matches2 looks below

   date         winning   losing        row    2013-03-03   usa       china          1    2013-03-05   usa       russia         2    2013-03-06   france    germany        3    2013-03-09   usa       russia         4    2013-03-11   usa       india          5    2013-03-15   usa       australia      6    2013-03-15   usa       new zealan     7 

as data shows, usa has won >3 games in row. how write code capture sequence ?

you can sequence of joins:

select m1.*, m2.date, m3.date matches2 m1 join      matches2 m2      on m2.row = m1.row + 1 , m2.winner = m1.winner join      matches2 m3      on m3.row = m2.row + 1 , m3.winner = m2.winner join      matches2 m4      on m4.row = m3.row + 1 , m4.winner = m3.winner;