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!

What do you mean, "can't find a solution"? Are you looking for "code to go"? — Sergio Tulentsev, Apr 28 '12 at 12:16
Is this what you're looking for http://stackoverflow.com/questions/3614666/mysql-get-row-position-in-order-by — John, Apr 28 '12 at 12:18
Yes, I am looking for the code. I tried some things with the help from other Stackoverflow guides, but I can't get it work:-( — Jordy, Apr 28 '12 at 12:18

Andrius Naruševičius · Accepted Answer · 2012-04-28T13:10:55.263

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

score 2 · Answer 2 · answered Apr 28 '12 at 12:20

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;

score 0 · Answer 3 · answered Apr 28 '12 at 12:17

0

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).

answered Apr 28 '12 at 12:17

Denys Séguret

372,613
87
782
758

Thank you very much. But what should I do: making a MySQL query or check it with PHP in a for loop? I mean: what is faster if there are >40.000 users? – Jordy Apr 28 '12 at 12:19
I updated a little. Except if you have a very small table, you have to maintain the rank. – Denys Séguret Apr 28 '12 at 12:20
Ok, so you mean that I should store the rank in the table? And that I shouldn't calculate it on the rankingspage? – Jordy Apr 28 '12 at 12:22
Yes. You should handle separately the computation (for example when you change the values or in batch) and the display. Because all sql based computations are slow. – Denys Séguret Apr 28 '12 at 12:24
Variables aren't needed for this. – Andrius Naruševičius Apr 28 '12 at 12:25
Thanks, but for example 'Andrius Naruševičius' answer, isn't that good and fast query to check the users rank? – Jordy Apr 28 '12 at 12:26
@Jordy : try it. His answer is simple. If it's good enough for your need, keep it. And the day you have more records, use the usual way of maintaining the rank. – Denys Séguret Apr 28 '12 at 12:27
27k test records and the result is returned instantly – Andrius Naruševičius Apr 28 '12 at 12:49
Thanks for the test. I think your answer is the solution. Thank you! – Jordy Apr 28 '12 at 13:07

Kai · Answer 4 · 2012-04-28T12:26:21.703

0

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

edited Apr 28 '12 at 12:26

answered Apr 28 '12 at 12:20

Kai

424
5
16

Pretty sure he did not ask for php code not only he did not even mention php. – Andrius Naruševičius Apr 28 '12 at 12:23
I don't know whats better and faster? PHP or MySQL? – Jordy Apr 28 '12 at 12:26
I was just inducing the following line: `On the top of the page the variable $user_id is set.` – Kai Apr 28 '12 at 12:27
2

He mentions PHP in the comments to my answer, so this may indeed interest him. No need to downvote. – Denys Séguret Apr 28 '12 at 12:36

score 0 · Answer 5 · answered Oct 09 '13 at 12:13

0

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

answered Oct 09 '13 at 12:13

Pradeesh Kumar

223
2
14

Get user rank with MySQL

5 Answers5