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)

Checkout the following post: http://stackoverflow.com/questions/3126972/mysql-row-number — Andy, Jan 14 '13 at 01:03

Mahmoud Gamal · Accepted Answer · 2013-01-14T01:04:20.347

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

if have records with duplicate USERID how i can edit this query? thank! — Marco Caltagirone, May 26 '14 at 16:37

score 2 · Answer 2 · answered Jan 14 '13 at 01:25

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.

SQL Find Position in table

2 Answers2