Trying to understand SQL variable syntax

Question

I am a noob in programming and I found this code block from a discussion page on leetcode that I find really difficult to understand:

SELECT
  Score,
  @rank := @rank + (@prev <> (@prev := Score)) Rank
FROM
  Scores,
  (SELECT @rank := 0, @prev := -1) init
ORDER BY Score desc

Any explanation would be very helpful for my understanding.

This is used to rank players based on score. Similar answers here: https://stackoverflow.com/questions/3333665/rank-function-in-mysql — Salman A, Oct 22 '20 at 11:37
This is a dirty hack to simulate window functions in MySQL versions that didn't support them. — , Oct 22 '20 at 11:38
Moreover, this code uses undocumented trick - in a comparing operator `<>` left operator is fixed before right operand calculation whereas the most inner parenthesis should cause the assigning firstly. This results in one more thin point - in shown code both variables stores string values whereas after removing the most inner parenthesis (`(@prev <> @prev := Score)`) they become numeric (you can see this in CLI - by column value justifying). — Akina, Oct 22 '20 at 12:08

score 1 · Answer 1 · answered Oct 22 '20 at 11:38

The only thing you need to know is that the correct syntax is:

select s.*,
       dense_rank() over (order by score desc) as rank
from scores s
order by s.score desc;

That use of variables is outdated syntax in MySQL and MariaDB. The assignment of variables in SELECT statements was deprecated in MySQL 8.0. This particular has now been replaced by the appropriate standard SQL functions.

Even your version is not going to work in all (older) versions of those databases. There is no guarantee that the ORDER BY is processed before the variable assignments -- and working around that further complicates the query.

Trying to understand SQL variable syntax

1 Answers1