178. Rank Scores-CodePudding

I tried to solve the question with the subquery approach and I am getting a behaviour I cant understand.

select score,
    (select count(distinct s1.score) from scores s1 
     where s1.score > s2.score) 1 as 'rank' 
from scores s2 
order by s2.score desc;

The above code runs perfectly but :

In line 3 of the query if I try to use rank instead of 'rank' leetcode compiler gives an error.
In the last line if I try to order it by rank it shows an error while rank by score works fine.

Please explain why it is happening. Many thanks for considering my request.

CodePudding user response：

Starting in MySQL 8 , RANK has become a reserved MySQL keyword. This is due to that MySQL 8 introduces an analytic function called RANK. So, if you want to use RANK as an alias, you should escape it in backticks or double quotes:

SELECT score,
    (SELECT COUNT(DISTINCT s1.score) FROM scores s1
     WHERE s1.score > s2.score)   1 AS `rank`
FROM scores s2
ORDER BY `rank` DESC;