MySQL select语句 - 如何计算当前排名

Question

I have a table called user_rankings where votes (voted) are stored for each user. I want to display the current ranking of users (this week) that depends on how much votes the user got.

example to clarify:

• RANK-NR, USERNAME, VOTED,
• 1, name1, 18 times
• 2, name1, 16 times
• (my ranking here), myname, 13 times

In this example my ranking should be 3. If I'd have 17 votes, I would be number 2. If there would be five users above me, I would be number 8. I guess you get the point.

Now I can display the ranking number easily with an incrementing $i in PHP. But I only want to show a list limited to ten users (a top ten list) and directly after that my current ranking, if I'm not already in that top ten list. So I'm just wondering how to get my exact ranking number using MySQL. I'm assuming to have hundreds of users in this list with a different amount of votes.

This is my statement at the moment:

SELECT 
`voted`
FROM `users_ranking` 
WHERE 
`uid`='".$_SESSION['uid']."' 
AND 
WEEKOFYEAR(`date`)=WEEKOFYEAR(NOW()) 
LIMIT 1

Answer 1

I just found out myself that this solution works:

SELECT *
FROM 
(
  SELECT  @ranking:= @ranking + 1 rank,
          a.`uid`
  FROM    `users_ranking` a, (SELECT @ranking := 0) b
  ORDER BY a.`votes` DESC
) s
WHERE `uid`='".$_SESSION['uid']."' 
AND 
WEEKOFYEAR(`date`)=WEEKOFYEAR(NOW()) 
LIMIT 1