Write mySQL to get a sequence

Question

I need to get the sequence of win or lost for a specific sport team. For example:

TABLE : RESULTS

teamid    win  eventdate
======    ===  =========
1         0    26/01/2014
1         0    25/01/2014
2         1    26/01/2014
1         0    24/01/2014 
1         1    21/01/2014

It should return teamid 1 has a sequence of 3 lost. But how in sql can I do that?

So what if team 1 had a sequence of 3 losses, followed by 4 wins, followed by 2 losses; and team 2 had a sequence of 7 wins and 8 losses? What result would you expect? — eggyal
– eggyal, Commented Jan 26, 2014 at 20:23

eggyal · Accepted Answer · 2014-01-26 20:26:35Z

1

MySQL doesn't support analytic functions, but one way of counting sequential wins/losses is via user variables:

SELECT   @seq    := IF(@teamid<=>teamid AND @win<=>win,@seq,0)+1 seq,
         @teamid := teamid teamid,
         @win    := win win
FROM     RESULTS, (SELECT @teamid:=NULL, @win:=NULL) init
ORDER BY teamid, STR_TO_DATE(eventdate, '%d/%m/%Y')

This query can be used as a basis for performing further analysis. For example, to obtain the longest streaks (both winning and losing) by team:

SELECT teamid, win, MAX(seq) FROM (
  SELECT   @seq    := IF(@teamid<=>teamid AND @win<=>win,@seq,0)+1 seq,
           @teamid := teamid teamid,
           @win    := win win
  FROM     RESULTS, (SELECT @teamid:=NULL, @win:=NULL) init
  ORDER BY teamid, STR_TO_DATE(eventdate, '%d/%m/%Y')
) t GROUP BY teamid, win

answered Jan 26, 2014 at 20:26

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Write mySQL to get a sequence

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related