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.