Works in MYSQL 5.7, Syntax Error in MYSQL 8.0
-
Hello My query to get a ranked leaderboard works on MYSQL 5.7 but gives me errors now that I upgraded to MYSQL 8.0
mysql> SELECT username,score,level, FIND_IN_SET(score,(SELECT GROUP_CONCAT(score ORDER BY score ASC)FROM highscore WHERE game = 0 AND level = 4 AND user = 10 )) AS rank FROM highscore WHERE game = 0 AND level = 4 AND user = 1 ORDER BY rank LIMIT 10;
ERROR 1064 (42000): 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 'rank FROM highscore WHERE game = 0 AND level = 4 AND user = 1 ORDER BY rank LIMI' at line 1 Any ideas? Thank you Team!
-
Hello My query to get a ranked leaderboard works on MYSQL 5.7 but gives me errors now that I upgraded to MYSQL 8.0
mysql> SELECT username,score,level, FIND_IN_SET(score,(SELECT GROUP_CONCAT(score ORDER BY score ASC)FROM highscore WHERE game = 0 AND level = 4 AND user = 10 )) AS rank FROM highscore WHERE game = 0 AND level = 4 AND user = 1 ORDER BY rank LIMIT 10;
ERROR 1064 (42000): 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 'rank FROM highscore WHERE game = 0 AND level = 4 AND user = 1 ORDER BY rank LIMI' at line 1 Any ideas? Thank you Team!
-
According to the following 'rank' was added as a keyword in MySQL 8 MySQL :: MySQL 8.0 Reference Manual :: 9.3 Keywords and Reserved Words[^]
Thank you so much. Simply changed "rank" to "ranklist" and it has resolved the issue.
-
Hello My query to get a ranked leaderboard works on MYSQL 5.7 but gives me errors now that I upgraded to MYSQL 8.0
mysql> SELECT username,score,level, FIND_IN_SET(score,(SELECT GROUP_CONCAT(score ORDER BY score ASC)FROM highscore WHERE game = 0 AND level = 4 AND user = 10 )) AS rank FROM highscore WHERE game = 0 AND level = 4 AND user = 1 ORDER BY rank LIMIT 10;
ERROR 1064 (42000): 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 'rank FROM highscore WHERE game = 0 AND level = 4 AND user = 1 ORDER BY rank LIMI' at line 1 Any ideas? Thank you Team!
Perhaps you should get together with your classmate / colleague: I need to generate rank in mysql v5.7.31[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer