oliver79

If I knew how to do so I would NOT post in a forum to get some help with this 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.

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.

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.

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