CODEWITHSUNDEEP
mysqlsqldense-rank
Tushar SINGH
Tushar SINGH

Reputation: 25

178. Rank Scores

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 :

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

Upvotes: 0

Views: 111

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

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;

Upvotes: 4

Related Questions