Make a leaderboard ranking from table in sql

user_18830246 0
Viewed 59

I have a database with names and their scores and I'd like to insert into the table the rank according to the score. My database looks a bit like this: phpmyadmin database

I would like to make it so when the score is higher than a rank the ranks change in the database. So if there were a player 1 with 500 points and a player 2 with 1500 points, the code would insert the rank into the rank column corresponding with the player's name. Like this:

Rank Name Score
1 Player2 1500
2 Player1 500
mysql sql
1 Answers

Don't store the RANK in the database calculate it on the fly when you read the data using the RANK() function.

SELECT
    Player,
    Score,
    RANK() OVER(ORDER BY Score DESC) AS 'RANK'
FROM
    PlayerScore
ORDER BY
    Score DESC

If you can have tied scores you can use DENSE_RANK(). This would give you ranks of 1,2,2,3. RANK would just give you 1,2,2,4

user_13533240
Related