Ollivander's Inventory challenge: Error showing syntax error in row_number() over(partition by ) in Mysql compiler

Question

select id,
       age,
       coins_needed,
       power
from(select wands.id as id,
       wands.code as code,
       wands_property.age as age,
       wands.coins_needed as coins_needed,
       wands.power as power,
       wands_property.is_evil as is_evil,
       row_number() over(partition by wands.code,wands.power order by wands.code,wands.power,wands.coins_needed) as rownum
from wands inner join wands_property
on wands.code = wands_property.code
order by wands.code,wands.power) sub
where is_evil = 0 and rownum = 1
order by 4 DESC,2 DESC

I was solving a challenge in hacker rank where I had to use row_number() and in hacker rank's MySQL compiler there was a syntax error as follows:-

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 '(partition by wands.code,wands.power order by wands.code,wands.power,wands.coins' at line 11

I tried this same query in MySQL workbench and to my surprise the query was running and did not show any error. Can any one explain me what is the real problem here.

Answer 1

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 '(partition by ....' at line 11

If you got this error then it could be that you are running under an old mysql/mariadb

MySQL/MariaDB introduced the ROW_NUMBER() function and the other window functions since version 8.0 for Mysql and 10.2 for MariaDB.