[Current winning streak query]

If I knew how to do so I would NOT post in a forum to get some help with this query...

[Current winning streak query]

I tried to somehow figure it out but it seems as a newbie regarding mySQL I just can`t.

 

I read this article on detecting runs or streaks in data (SQL) but still I found no way.

http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data

And honestly I also dont understand what you ment with combining the 2 queries which you suggested.

 

The solution is right before my eyes...I know,....but I hope you can give me another little hint on how to

do that.

 

Thanks.

[Current winning streak query]

Cool, your code works fine without annoying extra tables, thanks a lot !

 

And Arsenals last homegame was tied (5.5.2012 00:00:00 Arsenal Norwich 3 3)

You didn´t scroll down ;o)

 

Current winning streak is defined by how many games (in this case playing at home) a team won in a row counting from last game down.

[Current winning streak query]

Thank you all for your response.

Well, it seems to be a problem with the ID(primary key) field. The query itself is correct. Now I am doing this and it works just fine. I`d rather use a real temporary table but mysql docs say "You cannot refer to a TEMPORARY table more than once in the same query"

CREATE table mytemp (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     PRIMARY KEY (id)) select date, home, away, FTH, FTA, league from engp
order by home, date asc;

SET @count=0;

SELECT a.home
     , MAX(@count:=IF(b.FTH > b.FTA, @count + 1, 1)) best_streak
  FROM
     ( SELECT x.*
            , COUNT(*) rank
         FROM mytemp x
         JOIN mytemp y
           ON y.home = x.home
          AND y.league = x.league
          AND y.date <= x.date
        WHERE x.league = 'Premier League 11/12'
        GROUP
           BY id 
     ) a
  LEFT
  JOIN
     ( SELECT x.*
            , COUNT(*) rank
         FROM mytemp x
         JOIN mytemp y
           ON y.home = x.home
          AND y.league = x.league
          AND y.date <= x.date
        WHERE x.league = 'Premier League 11/12'
        GROUP
           BY id 
     ) b
    ON a.rank = b.rank  + 1
   AND b.home = a.home
WHERE a.FTH > a.FTA 
GROUP
    BY a.home;

drop table mytemp;

 

This outputs :

Arsenal		5
Aston Villa	1
Blackburn	1
Bolton		2
Chelsea		4
Everton		4
Fulham		2
Liverpool	2
Man City	15
Man Utd		7
Newcastle	4
Norwich		2
QPR		5
Stoke		2
Sunderland	3
Swansea		3
Tottenham	6
W.B.A.		2
Wigan		4
Wolves		2

 

These are the max winning streaks for each team season 11/12 during the whole season.

But what I really need is their current winning streak. For instance :

20.8.2011 00:00:00	Arsenal	Liverpool	0	2	
10.9.2011 00:00:00	Arsenal	Swansea		1	0	
24.9.2011 00:00:00	Arsenal	Bolton		3	0	
16.10.2011 00:00:00	Arsenal	Sunderland	2	1	
23.10.2011 00:00:00	Arsenal	Stoke		3	1	
5.11.2011 00:00:00	Arsenal	W.B.A.		3	0	
26.11.2011 00:00:00	Arsenal	Fulham		1	1	
10.12.2011 00:00:00	Arsenal	Everton		1	0	
27.12.2011 00:00:00	Arsenal	Wolves		1	1	
31.12.2011 00:00:00	Arsenal	QPR		1	0	
22.1.2012 00:00:00	Arsenal	Man Utd		1	2	
4.2.2012 00:00:00	Arsenal	Blackburn	7	1	
26.2.2012 00:00:00	Arsenal	Tottenham	5	2	
12.3.2012 00:00:00	Arsenal	Newcastle	2	1	
24.3.2012 00:00:00	Arsenal	Aston Villa	3	0	
8.4.2012 00:00:00	Arsenal	Man City	1	0	
16.4.2012 00:00:00	Arsenal	Wigan		1	2	
21.4.2012 00:00:00	Arsenal	Chelsea		0	0	
5.5.2012 00:00:00	Arsenal	Norwich		3	3

 

So Arsenals max winning streak is 5 in a row, but because they tied their last homegame the current streak would be 0. Any ideas how to update the query in order to getthis done ? Thanks.

[Current winning streak query]

This is some sample data of a table named "engp":

ID DATE                 HOME      AWAY       FTH FTA  SEASON

1 22.04.2012 00:00:00  Man Utd   Everton    4   4    Premier League 11/12
2 05.11.2011 00:00:00  Arsenal   W.B.A.     3   0    Premier League 11/12
3 01.10.2011 00:00:00  Wolves    Newcastle  1   2    Premier League 11/12
4 22.04.2012 00:00:00  Liverpool W.B.A.     0   1    Premier League 11/12
5 02.10.2011 00:00:00  Swansea   Stoke      2   0    Premier League 11/12
6 07.04.2012 00:00:00  Chelsea   Wigan      2   1    Premier League 11/12
7 26.02.2012 00:00:00  Arsenal   Tottenham  5   2    Premier League 11/12
8 11.09.2011 00:00:00  Norwich   W.B.A.     0   1    Premier League 11/12
9 07.04.2012 00:00:00  W.B.A.    Blackburn  3   0    Premier League 11/12
10 14.08.2011 00:00:00  Stoke     Chelsea    0   0    Premier League 11/12

 

What I need to do is find the teams in current season with the longest sequence of homewins (FTH>FTA) during the last games played until today. (Current sequences). One suggested the following query but the problem is that this query targets the longest winning streak of each team during the whole season so far and not the latest winning streak for each team. 2nd problem is that the query does not even work for that and provides wrong results.

SET @count=0;

SELECT a.home
     , MAX(@count:=IF(b.FTH > b.FTA, @count + 1, 1)) best_streak
  FROM
     ( SELECT x.*
            , COUNT(*) rank
         FROM engp x
         JOIN engp y
           ON y.home = x.home
          AND y.league = x.league
          AND y.date <= x.date
        WHERE x.league = 'Premier League 11/12'
        GROUP
           BY id
     ) a
  LEFT
  JOIN
     ( SELECT x.*
            , COUNT(*) rank
         FROM engp x
         JOIN engp y
           ON y.home = x.home
          AND y.league = x.league
          AND y.date <= x.date
        WHERE x.league = 'Premier League 11/12'
        GROUP
           BY id
     ) b
    ON a.rank = b.rank  + 1
   AND b.home = a.home
WHERE a.FTH > a.FTA 
GROUP
    BY a.home;

 

Any help would be greatly appreciated