0

I was trying to solve a HackerRank SQL problem using Rank() and Row_number(), but kept getting errors. I then tried making a simple window function on a simple table

SELECT RANK() OVER (PARTITION BY countrycode ORDER BY population DESC) AS rank FROM city;

from https://www.hackerrank.com/challenges/average-population-of-each-continent/problem?isFullScreen=true ,and sure enough: "ERROR 1064 (42000) at line 1: 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 city' at line 1".

Similar code works for me when coding MySQL through my command line, and the manual doesn't show any errors. Does anyone know why?

Inedible_Sun
  • 25
  • 5
  • Post full error message. – forpas Jan 15 '22 at 18:32
  • 1
    This might be a version error as window functions are allowed in MySQL only from version 8.0 or higher. – Ankit Bajpai Jan 15 '22 at 18:37
  • 1
    `rank` is a reserved word in MySql 8.0 Enclose it inside backticks. – forpas Jan 15 '22 at 18:37
  • Like so: \`Rank()\`, that still doesn't work :/. Unfortunately it is version 8.0.20 – Inedible_Sun Jan 15 '22 at 18:41
  • 1
    No. Your column alias `AS rank` could be changed to quote "rank". MySQL uses backtics in the default configuration, but the standard would use double quotes... or just don't use reserved words for column aliases. – Jon Armstrong Jan 15 '22 at 18:42
  • Apparently HackerRank uses different versions of SQL for different problems. That's why everybody in this post was right. For the harder problems, I had the right syntax but the version was 5.0.X – Inedible_Sun Jan 15 '22 at 19:13
  • 1
    If the version of MySql that yo use was 5.x then the error message would be different: *...near '(PARTITION BY countrycode ORDER BY population DESC) AS rank FROM city' at line 1*. Check this: https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=2bd947db40ec5efb910f04a9043a7694 The error message that you mention in your question could be thrown only in version 8.x. Check this: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2bd947db40ec5efb910f04a9043a7694 – forpas Jan 15 '22 at 20:00

0 Answers0