Geek Answers Handbook

SQL Find Position in Table

I have a table in mySql that has a user id and ratings.

What I would like to do is organize the table by points (simple), but then find where the specific user ID is in the table.

So far, I would:

SELECT * FROM table_score ORDER BY Score DESC

How can I find where userID = '1234' (i.e. record 10 of 12)

+6
source share
2 answers

The following query will give you a new UserRank column that sets 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 |

You can then put this query in a subquery and filter using userId to get this user rating. Sort of:

 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

+10
source

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 the account and userid, it will be quite effective.

+2
source

More articles:


All Articles