Search code examples
phpmysqlranking

Number of users in a ranking MySQL query

To get the ranking of the user 3 I am using this query (It works fine):

$sql = "SELECT 
score,
FIND_IN_SET(score, 
(
SELECT GROUP_CONCAT(score 
ORDER BY score DESC)
FROM results)
) AS rank
FROM results
WHERE user_id = 3
";

Table structure:

user_id - score

Now I want to give the user 3 some points. My formule is:

Number of score (Or users) - rank + 1.

So if I have 20 users (Or score) and the ranking of the user 3 is 10, so the points will be:

20 - 10 + 1 = 11 points.

PS: users (Or score) = some users have more than one score.

How can I get the number of score (Or users) using this mysql query?

SQL FIDDLE http://sqlfiddle.com/#!9/ff4505/4

Solution

  • Check this ... I hope it help you ...

    SELECT user_id,@all_user := (SELECT COUNT(*) FROM `results`) as all_count,((@all_user-score)+1) as rank FROM `results` WHERE user_id = 3