How to calculate longest undefeated streak using MySQL?

Question

I'm creating a web page that lists various longest streaks for a single team, but I've encountered an issue when trying to calculate a streak for a combination of result types involved.

The data is presented in a table as follows...

date         result   gf   ga   type     compfull
--------------------------------------------------
1980-08-16      W     3    0   league    Division 1
1980-08-19      L     1    2   league    Division 1
1980-08-23      W     3    1   league    Division 1
1980-08-26      W     2    0   league    Division 1
1980-08-30      D     2    2   league    Division 1
and so on...

Using the following query (or similar), I can determine the longest run of wins or losses or even games scored in.

SELECT result, type, MIN(date) as StartDate, MAX(date) as EndDate, COUNT(*) as Games 
FROM (SELECT result, type, date, compfull, (SELECT COUNT(*) 
FROM resultengine R 
WHERE R.result <> RE.result
AND R.date <= RE.date) as RunGroup
FROM resultengine RE) A WHERE result='W' GROUP BY result, RunGroup ORDER BY Games

This is based on the excellent resource I found here. It does exactly what I want it to and tells me what I want to know - the same applies if I change to

result='L'

What I can't work out is how to calculate the longest streak of undefeated games i.e. the longest run without an L. And it's the same vice-versa.

I've tried the following query to no avail:

SELECT result, type, MIN(date) as StartDate, MAX(date) as EndDate, COUNT(*) as Games 
FROM (SELECT result, type, date, compfull, (SELECT COUNT(*) 
FROM resultengine R 
WHERE R.result <> RE.result
AND R.date <= RE.date) as RunGroup
FROM resultengine RE) A WHERE result!='W' GROUP BY result, RunGroup ORDER BY Games

I've also tried altering the query to:

WHERE result='W' OR result='D'

Again, this doesn't work. Both attempts mirror the query that's used to deliver the longest streak of Ws or Ls - and a manual count of my data tells me that isn't correct. I'm no doubt missing something simple here, but how can I execute that query so it tells me the longest streak of results with either Ls or Ws?

Answer 1

Extended from your code sample the following gives runs of wins/draws. The problem was with 'WHERE R.result <> RE.result' which always assigned different groupings for any different result code. Here I change that clause (and some others) to group 'W' and 'D' together to make a single code:

SELECT result, TYPE, MIN(DATE) AS StartDate, MAX(DATE) AS EndDate, COUNT(*) AS Games 
FROM (SELECT result, TYPE, DATE, compfull, (SELECT COUNT(*) 
  FROM resultengine R 
  WHERE IF(R.result IN ('W','D'),1,0) <> IF(RE.result IN ('W','D'),1,0)
  AND R.date <= RE.date) AS RunGroup
FROM resultengine RE) A WHERE result IN ('W','D') GROUP BY IF(result IN ('W','D'),1,0), RunGroup ORDER BY Games

Answer 2

This query will give you max undefeated sequence for a single team (the table contains data for single team:

select MAX(final.win_seq_count) from

(Select date,result,gf,ga,type,compfull,
 @seq_count:=if(result="L",0,@seq_count:=@seq_count+1) as win_seq_count,
from resultengine,(select @seq_count:=0) t


order by date) final

Note: Code may contain small syntactic error.