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,