Get user rank with MySQL

Question

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!

Answer 1

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

Answer 2

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;