MySQL Winning Streak for one team

You'll have to implement some MySQL variables to help handle this more efficiently than multiple query/join/group by. This has a single pass through all the records, then once more getting the max per type of win/loss (or tie). I'm assuming the data you've provided is for a single time, and the date is the obvious order of the games... Points For is the team you are interested in, and points against is whoever the opponent is. That said, my "alias" name will be "YourResultSingleTeam".

The inner query will pre-determine the status of the game as "W"in or "L"oss. Then, see if that value is the same as the previous instance for the team. If so, add 1 to the existing win/loss counter. If not, set the counter back to 1. Then, preserve the status of the current game back into the "LastStatus" value for comparison to the next game.

After that is done, its a simple game result, max() grouped by the game result status

select
      StreakSet.GameResult,
      MAX( StreakSet.WinLossStreak ) as MaxStreak
   from
      ( select YR.Date,
               @CurStatus := if( YR.PointsFor > YR.PointsAgainst, 'W', 'L' ) as GameResult,
               @WinLossSeq := if( @CurStatus = @LastStatus, @WinLossSeq +1, 1 ) as WinLossStreak,
               @LastStatus := @CurStatus as carryOverForNextRecord
            from 
               YourResultSingleTeam YR,
               ( select @CurStatus := '',
                        @LastStatus := '',
                        @WinLossSeq := 0 ) sqlvars
            order by
               YR.Date ) StreakSet
   group by
      StreakSet.GameResult

As offered by Nikola, if you want to consider "tie" games, we can adjust by just changing the @CurStatus to the case/when condition to

@CurStatus := case when YR.PointsFor > YR.PointsAgainst then 'W'
                   when YR.PointsFor < YR.PointsAgainst then 'L'
                   else 'T' end as GameResult,

Tags:

Mysql

Sql