How to use find in set for user rankings based on score?

Question

I have query like this,

SELECT * FROM users ORDER BY score

So, the result is like this.

Array
(
    [0] => stdClass Object
        (
            [userid] => 3
            [user] => John Doe
            [score] => 50
        )

    [1] => stdClass Object
        (
            [userid] => 1
            [user] => Mae Smith
            [score] => 38
        )

    [2] => stdClass Object
        (
            [userid] => 2
            [user] => Mark Sam
            [score] => 26
        )
)

But, I want to add a rank using find_in_set query. So the result might be like this. So that the user can view their ranks when they login to their account.

Array
(
    [0] => stdClass Object
        (
            [userid] => 3
            [user] => John Doe
            [score] => 50
            [rank] => 1
        )

    [1] => stdClass Object
        (
            [userid] => 1
            [user] => Mae Smith
            [score] => 38
            [rank] => 2
        )

    [2] => stdClass Object
        (
            [userid] => 2
            [user] => Mark Sam
            [score] => 26
            [rank] => 3
        )
)

I tried this one.

$listOfUser = array();
foreach($users as $user) {
   $listOfUser[] = $user->userid;
}

And used another query

$userid = 2 // => id of loggedin user
SELECT *, find_in_set($userid, $listOfUser) as rank FROM users where userid=$userid ORDER BY score

So, I got this result

Array
(

    [1] => stdClass Object
        (
            [userid] => 2
            [user] => Mark Sam
            [score] => 26
            [rank] => 3
        )
)

Which is somehow correct. But, is there another way of querying that result using only one SQL query and without using foreach loop?

Something like this.

$userid = 2 // => id of loggedin user
SELECT *, find_in_set($userid, (SELECT * FROM users ORDER BY score)) as rank FROM users where userid=$userid ORDER BY score

But I got this error Subquery returns more than 1 row

Have you considered using a join? Or is there something I am missing? — ka_lin, Aug 08 '18 at 09:18
Could you be more clear about what you want to be displayed? Do you want each user to see people with similar rank? — Rafael, Aug 08 '18 at 09:30
No, If the user logged in on his account, he can see his rank based on his score — Codeblooded Saiyan, Aug 08 '18 at 09:41

Roman Hocke · Accepted Answer · 2018-08-08T09:45:56.583

4

If You don't insist on using find_in_set, you can get result with simple join. You ask for list of users (p) and for each user you ask, how many users have better score than him or her (c):

SELECT p.userid, COUNT(c.userid) AS rank
FROM users AS p
LEFT JOIN users AS c ON c.score > p.score
GROUP BY p.userid

This works even if you add other conditions, like WHERE p.userid = 123. If more users have the same score, the ranks would look like 0,1,2,2,2,5,6.

edited Aug 08 '18 at 09:45

answered Aug 08 '18 at 09:25

Roman Hocke

4,137
1
20
34

It works huh! But, I want the rank to start with one not with zero. How may I able to do that? – Codeblooded Saiyan Aug 08 '18 at 09:43
@JSmith `SELECT p.userid, COUNT(c.userid) + 1 AS rank ...` – Roman Hocke Aug 08 '18 at 09:44

score 0 · Answer 2 · answered Aug 08 '18 at 09:28

0

In your query, you can add counter, like this:

set @n:=0; 
SELECT @i := @i + 1 AS rank, * FROM users ORDER BY score

answered Aug 08 '18 at 09:28

Vasyl Zhuryk

1,228
10
23

score 0 · Answer 3 · answered Aug 08 '18 at 09:29

The rank here is relative to the score distribution across all users. I believe you should try something originally proposed in this answer:

SELECT users.*, 
   @rownum := @rownum + 1 as rank
FROM users
CROSS JOIN (select @rownum := 0) r
ORDER BY score DESC

What it does is basically order all users by score, and assign each of them an incremental value "rank". So the top scorer would have a rank of 1, the second scorer would have a rank of 2 etc.

Keep in mind that this solution is not "fair" - each user will have a different rank, even if all users have the same score. If you try to rank users as they do in sports (if two top competitors have the same score, they both take 1st place, and the next best competitor takes 3rd place, not second), you should think of a different solution.

But if I select specific user the rank is always 1 when I add sample: `where id=3` — Codeblooded Saiyan, Aug 08 '18 at 09:37
You could wrap the above query in a SELECT * FROM (above subquery) tmp WHERE tmp.id = 3 - and there you have your result. — Kleskowy, Aug 08 '18 at 10:22

How to use find in set for user rankings based on score?

3 Answers3