I have a table full of individual bowling scores (table: bowlers) for a charity bowling tournament. I'm trying to pull out the top individual scores for males and females over a possible 3 games of bowling, link them up with the team name from a different table (table: teams), and then order the top scores in descending order.
I'm starting with the male scores and have an SQL query that I think should work to select the top score for each person across their possible three games, then order all of the records in descending order. I based it off of the answer I found to a similar question on Stack Overflow here: http://stackoverflow.com/a/6871572
Here's my query:
$topmalebowler = "SELECT bowlers.bowler_name, teams.team_name,
(SELECT MAX(v) FROM (VALUES (bowlers.game_1_score),
(bowlers.game_2_score), (bowlers.game_3_score)) AS value(v))
as TopScore FROM bowlers INNER JOIN teams ON
bowlers.team_id=teams.team_id WHERE bowlers.sex = 'M'
ORDER BY 'TopScore' DESC";
The code you originally posted was written for T-SQL and SQL Server, the Microsoft implementations of SQL. You are running MySQL, which sometimes has different syntax. As Terminus pointed out, the thing to use for MySQL is the
To avoid skipping rows with at least one NULL game score, you would structure the query like this:
SELECT bowlers.bowler_name, teams.team_name, GREATEST( IFNULL(bowlers.game_1_score, 0), IFNULL(bowlers.game_2_score, 0), IFNULL(bowlers.game_3_score, 0)) AS TopScore FROM bowlers INNER JOIN teams ON bowlers.team_id=teams.team_id WHERE bowlers.sex = 'M' ORDER BY 'TopScore' DESC