0

I am having this problem that I just can't figure out and I was wondering if anyone had any information on it.

I am currently running the below SQL (In a schema similar to here: demo)...

SELECT match_id, team, date, hometeam, awayteam, FTHG, FTAG, `2.5 goals`
FROM (
 SELECT 
 match_id, team, TEMP.date, hometeam, awayteam, FTHG, FTAG,
 @teamCounter:=IF(@prevHome=team,@teamCounter+1,1) teamCounter,
 @prevHome:=team,
CASE    WHEN FTHG + FTAG < 2.5 THEN 'UNDER'
    ELSE 'OVER'
END  AS `2.5 goals`
 FROM TEMP
 JOIN (
 SELECT DISTINCT date, hometeam team
 FROM TEMP
 UNION 
 SELECT DISTINCT date, awayteam
 FROM TEMP
 ORDER BY team, date DESC
 ) allgames
 ON TEMP.date = allgames.date
 AND (TEMP.hometeam = allgames.team
 OR TEMP.awayteam = allgames.team)
 JOIN (SELECT @teamCounter:=0) t
 ORDER BY team, TEMP.date DESC
 ) t 
WHERE teamCounter <= 6
ORDER BY team, date;

The SQL is based on a Stackoverflow answer I found Here, but basically I want it to show me the last 6 games that each team was involved in (either home or away).

The strange thing is, when I run this in MySQL, it will work in the first SQL session, but sessions created after that it will return vastly different results. I can then remove the match_id field from the outer SELECT list and the query will return to it's normal dataset, until another session is opened again... and then returning the match_id field will show the desired results as to begin with, starting us at the beginning again.

I'm not sure if the SQLFiddle I have linked to will recreate the problem but can anyone explain why this is happening?

Any help greatly appreciated!

Kev

Community
  • 1
  • 1
kevino_17
  • 3
  • 1
  • Any time you start using: `@teamCounter:=` within a query things get dicey fast. You could try setting all your variables to null or 0 before starting. (e.g.: `set @teamCounter = 0`) –  Jan 14 '14 at 20:42
  • Hi ebyrob, thanks for the reply. I have added the set @teamCounter = 0 to the start and the end of the SQL, and compiled it into a stored procedure. Now however I only get the desired 6 rows per team on the 2nd run of the procedure... any ideas? I'm stumped. – kevino_17 Jan 14 '14 at 20:59
  • Ah- added a set @prevHome = null; to the start and the end of the SQL and now works every time. Huge thanks for your help it really is appreciated. – kevino_17 Jan 14 '14 at 21:04
  • Glad it worked! I think there'd be a way to do it with a more standard-SQLish query, but if you got it working. I'm sure that'd be a bear so why not go with what ya got. Good luck. –  Jan 14 '14 at 21:32

0 Answers0