TMax TMax - 2 months ago 5
MySQL Question

SQL Error When Selecting Max Value from Multiple Columns in Tables

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";


I keep getting an error with it, though.

"Error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES (bowlers.game_1_score), (bowlers.game_2_score), (bowlers.g' at line..."

What am I doing wrong here? How can I make this code tie the room together?

Note: MySQL 5.5

Answer

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 GREATEST function.

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
Comments