CODEWITHSUNDEEP
mysqlranking
Jordy
Jordy

Reputation: 4809

Get user rank with MySQL

I read many topics about this problem but I can't find the solution.

I have a table (called users) with the users of my website. They have points. For example:

+-----------+------------+
| User_id   | Points     |
+-----------+------------+
| 1         | 12258      |
| 2         | 112        |
| 3         | 9678       |
| 4         | 689206     |
| 5         | 1868       |
+-----------+------------+

On the top of the page the variable $user_id is set. For example the user_id is 4. Now I would like to get the rank of the user by points (output should be 1 if the user_id is 4).

Thank you very much!

Upvotes: 11

Views: 11301

Answers (5)

Pradeesh Kumar
Pradeesh Kumar

Reputation: 223

This may help

SELECT @rank:=@rank+1 AS rank,`User_id`,`Points` FROM `users` u JOIN (SELECT @rank:=0) r ORDER BY u.Points DESC

Upvotes: 0

Andrius Naruševičius
Andrius Naruševičius

Reputation: 8578

SELECT 
    COUNT(*) AS rank 
FROM users 
WHERE Points>=(SELECT Points FROM users WHERE User_id=4)

Updated with some more useful stuff:

SELECT 
    user_id, 
    points, 
    (SELECT COUNT(*)+1 FROM users WHERE Points>x.points) AS rank_upper, 
    (SELECT COUNT(*) FROM users WHERE Points>=x.points) AS rank_lower 
FROM 
    `users` x 
WHERE x.user_id = 4

which includes the range of ranks user is at. So for example if the scores for first five places are 5 4 3 3 3, the result would be:

id points rank_upper rank_lower
id 5      1          1
id 4      2          2
id 3      3          5
id 3      3          5
id 3      3          5

Upvotes: 16

Kai
Kai

Reputation: 423

There's already a simple solution, just suited for your purpose.

Upvotes: 0

aleroot
aleroot

Reputation: 72636

This query should do what you want :

SELECT rank FROM (
   SELECT User_id, Points,
          FIND_IN_SET(
                Points,
                   (SELECT  GROUP_CONCAT(
                           DISTINCT Points 
                            ORDER BY Points   DESC
                        )
               FROM    users)
            ) as rank
    FROM   users )
WHERE User_id = 4;

Upvotes: 2

Denys Séguret
Denys Séguret

Reputation: 382102

If you don't want to do it outside mysql you'll need to use variables to compute the rank.

Here's a solution that describes exactly what you want :

http://www.fromdual.ch/ranking-mysql-results

You still need, it you want to have it directly for each record, to store it in the record and to update it yourself. There is no reasonable query that will give you directly the rank without storage on a real table (I mean not just a few hundreds records).

Upvotes: 0

Related Questions