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?

Solution 1

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.

Solution 2

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;