SQL Find Position in table

Question

I have a table in mySql which has the users ID and scores.

What I would like to do is organise the table by scores (simple) but then find where a certain user ID sits in the table.

So far I would have:

SELECT * FROM table_score
ORDER BY Score DESC

How would I find where userID = '1234' is (i.e entry 10 of 12)

Answer 1

The following query will give you a new column UserRank, which specify the user rank:

SELECT 
  UserID, 
  Score, 
  (@rownum := @rownum + 1) UserRank 
FROM table_score, (SELECT @rownum := 0) t 
ORDER BY Score DESC;

SQL Fiddle Demo

This will give you something like:

| USERID | SCORE | USERRANK |
-----------------------------
|      4 |   100 |        1 |
|     10 |    70 |        2 |
|      2 |    55 |        3 |
|   1234 |    50 |        4 |
|      1 |    36 |        5 |
|     20 |    33 |        6 |
|      8 |    25 |        7 |

Then you can put this query inside a subquery and filter with a userId to get that user rank. Something like:

SELECT
  t.UserRank
FROM
(
   SELECT *, (@rownum := @rownum + 1) UserRank 
   FROM table_score, (SELECT @rownum := 0) t 
   ORDER BY Score DESC
) t
WHERE userID = '1234';

SQL Fiddle Demo

Answer 2

For a given user id, you can do this with a simple query:

select sum(case when ts.score >= thescore.score then 1 else 0 end) as NumAbove,
       count(*) as Total
from table_score ts cross join
     (select ts.score from table_score ts where userId = '1234') thescore

If you have indexes on score and userid, this will be quite efficient.