CODEWITHSUNDEEP
mysqlsystemranking
forel
forel

Reputation: 33

mysql Ranking system based on user id

I have a question about the query originally from http://jimmod.com/blog/2008/09/displaying-row-number-rownum-in-mysql/

SELECT IF(@points=p.points, @rownum, @rownum:=@rownum+1) rank, 
       p.*, (@points:=p.points) dummy 
FROM `ranking` p, (SELECT @rownum:=0) x, (SELECT @points:=0) y 
ORDER BY `points` DESC

Result of the query is:

rank user_id name score
-----------------------
1      4    Barney  100
2      2    Ted      88
3      1    Marshall 76
3      3    Robin    76
4      5    Lily     68

How can I get the same rank result based on user id?

Query below is based on user id, but the rank of Robin will be 4, not 3 like it should be.

SELECT rank FROM (SELECT @rownum:=@rownum+1 rank, p.* 
FROM `ranking` p, (SELECT @rownum:=0) r 
ORDER BY `points` DESC) a WHERE a.user_id = 3

Upvotes: 1

Views: 171

Answers (1)

sgeddes
sgeddes

Reputation: 62841

Without testing it, seems like this should work using a subquery:

SELECT rank
FROM (
    SELECT IF(@points=p.points, @rownum, @rownum:=@rownum+1) rank, 
       p.*, (@points:=p.points) dummy 
    FROM `ranking` p, (SELECT @rownum:=0) x, (SELECT @points:=0) y 
    ORDER BY `points` DESC
) t
WHERE user_id = 3

Your query above displays 4 because you're not using RANK, but rather just ROW NUMBER, and Robin shows up after Marshall (even though they have the same score).

Good luck.

Upvotes: 1

Related Questions