MYSQL 5.7 Getting the row number

Question

I have a database called "playerrank" that has points column. I want to show on people's profile page their rank like this:

Rank: 3/1456

I tried using ROW_NUMBER() but it seems like my host has low version (5.7 i believe).its giving me errors.

Is there another way i can get the ranking of a player based on points other than ordering the db by points desc and getting the row number somehow?

Does players with the same points have to need same rank? if so you need to simulate `DENSE_RANK()` which is harder to pull off in MySQL versions under 8.0 with user variables then `ROW_NUMBER()` — Raymond Nijland, Jan 17 '19 at 16:09
It is easy but slow. Hint: rank of a player = 1 + count of players that are "lower" than it for some condition. This produces same results as RANK(). DENSE_RANK()... left as exercise. — Salman A, Jan 17 '19 at 18:19

score 14 · Answer 1 · answered Oct 12 '20 at 05:42

14

In MySQL 5.7, in a single query

SELECT 
    (@row_number := @row_number + 1) AS rnk, points
FROM yourTable,
(SELECT @row_number := 0) AS x
ORDER BY points DESC;

answered Oct 12 '20 at 05:42

aRvi

2,203
1
14
30

score 11 · Answer 2 · answered Jan 17 '19 at 16:07

One option to simulate row number in MySQL 5.7 uses session variables:

SET @row_number = 0;

SELECT 
    (@row_number:=@row_number + 1) AS rnk, points
FROM yourTable
ORDER BY points DESC;

Note that technically row number is not the same thing as rank, but I suspect that you do want row number here. In this case, if say three players were tied with the same number of points, they might have a different rank number assigned to them.

MYSQL 5.7 Getting the row number

2 Answers2

Linked