userid | points 1 | 456 2 | 3 3 | 1778 ... | ...
I used this function for years in MySQL 5 to receive the userrank:
SELECT userid, userrank FROM (SELECT @row_number:[email protected]_number+1 AS userrank, userid FROM `userpoints`, (SELECT @row_number := 0) r ORDER BY points DESC) t WHERE userid = 123
And it returned the userrank for userid
With MySQL 8 I only get
1 as value for
userrank with each userid I try.
What is the problem and how to fix this?
I tried the inner
SELECT alone, and this gives me the list of all userids with the correct userranks.
In MySQL 8, setting user variables as side-effects in expressions is deprecated. You should use window functions instead.
SELECT t.userid, t.userrank FROM ( SELECT ROW_NUMBER() OVER (ORDER BY points DESC) AS userrank, userid FROM `userpoints` ) t WHERE t.userid = 123;