1

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: https://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

Community
  • 1
  • 1
TMax
  • 59
  • 2
  • 10
  • 1
    I'm sure you copied one of these down wrong, but your code is referencing the `bowlers` table and your error is referencing the `bowlers_2016` table. – tschwab Sep 20 '16 at 02:18
  • You're right...fixed it. Thanks! – TMax Sep 20 '16 at 02:24
  • 1
    If you're using MySQL then you should look at the second most upvoted answer on the question you linked. The syntax you're using only works in Microsoft Sql Server –  Sep 20 '16 at 02:50
  • Thank you, Terminus! I tried the GREATEST function and it did work. Problem solved. If anyone has an idea for why this query didn't work, though, I am curious what I was missing. – TMax Sep 20 '16 at 03:06
  • Check that...problem not quite solved. GREATEST seems to ignore rows with null values in any of the columns it looks at. – TMax Sep 20 '16 at 03:35
  • @TMax I see your questions were answered; good! In case you haven't already, don't forget to up vote the answers that helped in the question you linked :) Also, for your reading pleasure: http://stackoverflow.com/questions/4747877/mysql-ifnull-vs-coalesce-which-is-faster –  Sep 20 '16 at 06:04

1 Answers1

2

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
tschwab
  • 1,056
  • 1
  • 12
  • 27
  • Thank you, tschwab. After experimenting with it a bit more, though, I've discovered that GREATEST doesn't seem to take into account rows with null values. For example, a bowler with a high score, but a value of 'null' for game_3_score. Is there a way to account for those and still use the GREATEST function? That was one of the reasons why I was trying to make the "MAX" function work, since it seemed to allow for null values. – TMax Sep 20 '16 at 03:34
  • 1
    My apologies, that answer only works for two columns. I updated my answer. – tschwab Sep 20 '16 at 03:52
  • That's it! Thank you, tschwab....I kept trying versions of your edits with no success, but this last one solved the problem. I very much appreciate your help with this! – TMax Sep 20 '16 at 04:01
  • Great! Glad I could be of help. – tschwab Sep 20 '16 at 04:02