Colin Brazier
asked on
Session variables in MySQL query - unreliable?
Here's a thing...the first time I run this query, the WHERE clause "WHERE team_rank <=6" is ignored, it shows all the data. Running second and subsequent times, it limits to 6.
Now if I leave it and come back to it, once again it shows all the data.
Are session variables known to be unreliable in MySQL? I have tried in both MySQL Workbench and SQL Maestro for MySQL.
Now if I leave it and come back to it, once again it shows all the data.
Are session variables known to be unreliable in MySQL? I have tried in both MySQL Workbench and SQL Maestro for MySQL.
DROP TEMPORARY TABLE IF EXISTS scores;
/* You can't do the union directly for the create table,
but you can make it a sub-select: */
CREATE TEMPORARY TABLE scores AS
SELECT * FROM
(
SELECT home_team AS Team, home_score AS f_score, away_score AS a_score, match_date,
CASE
WHEN home_score > away_score THEN 'W'
WHEN home_score < away_score THEN 'L'
ELSE 'D'
END AS wdl
FROM all_results
WHERE ar_fobg_team_code = '1'
AND ar_season_start = 2017
AND home_score REGEXP '[[:digit:]]'
UNION
SELECT away_team AS Team, away_score AS f_score, home_score AS a_score, match_date,
CASE
WHEN home_score > away_score THEN 'L'
WHEN home_score < away_score THEN 'W'
ELSE 'D'
END AS wdl
FROM all_results
WHERE ar_fobg_team_code = '1'
AND ar_season_start = 2017
AND home_score REGEXP '[[:digit:]]'
) AS the_data;
SELECT Team, sum(1) as 'P', sum(CASE WHEN wdl = 'W' THEN 1 ELSE 0 END) AS 'W',
sum(CASE WHEN wdl = 'D' THEN 1 ELSE 0 END) AS 'D', sum(CASE WHEN wdl = 'L' THEN 1 ELSE 0 END) AS 'L',
sum(f_score) AS 'F', sum(a_score) as 'A', sum(f_score - a_score) AS 'GD',
sum(CASE WHEN wdl = 'W' THEN 3 WHEN wdl = 'D' THEN 1 ELSE 0 END) AS 'Pts'
FROM
(
SELECT Team, f_score, a_score, match_date, wdl,
@team_rank := IF (@current_team = Team,
@team_rank + 1, 1)
AS 'team_rank',
@current_team := Team AS 'Current_team'
FROM scores
ORDER BY Team, match_date DESC
) AS the_outer_data
WHERE team_rank <= 6
GROUP BY Team
ORDER BY Pts DESC, GD DESC, F DESC, Team ASC;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER