CODEWITHSUNDEEP
javascriptmysqlsqlnode.js
Emrik Ahlström
Emrik Ahlström

Reputation: 105

MySQL get specific number from order of query result

I have a table called users, in the users table there is a column called rating. Since I am creating a leaderboard, I order the top 100 users by rating. Each ranking of the leaderboard is simply displayed by a code like this

<% let i = 0;
for(const row of users) { %>
 <%= i; %>. <%= row.username; %>
<% } %>

Then the leaderboard gets displayed simply like this

1. John
2. Doe
3. Mike
4. Peter
....
....

Now I also want to show the specific rank of the player viewing the page. Since I have over 5,000 registered accounts, I want to display something like this:

Your leaderboard placing:
2854. Erik

But I have to get the rank for Erik that is 2854 without having to loop trough the whole users table in order to access that number. How do I do this efficiently?

Upvotes: 0

Views: 78

Answers (1)

Đăng Khoa Đinh
Đăng Khoa Đinh

Reputation: 5411

In MySQL, there is RANK() operation that calculates the ranking of rows using ORDER BY. For example :

SELECT
    username, rating,
    RANK() OVER (
        ORDER BY rating
    ) rank
FROM
    leaderboard;

The output will have 3 columns: username, rating, and rank. The rank columns is the ranking calculate from rating

You can find more information from these links :

--- EDIT ---

To get the rank of a specific user, I think we will run the WHERE clause on the ranking table.

WITH ranking AS 

(SELECT
    username, rating,
    RANK() OVER (
        ORDER BY rating
    ) rank
FROM
    leaderboard;
)

SELECT * FROM ranking WHERE username = "Erik"

Upvotes: 1

Related Questions