如何从mysql db获得一个用户的排名？

Question

I have like this table at my mysql db for highscore.

and I got SQL for get rank of all users.

SELECT b.id
 , b.name
 , @rank_cnt := IF(@prev_score = b.score,@rank_cnt,@rank_cnt+1) AS rank
 , @prev_score := b.score AS score
    FROM BBR b
       CROSS
        JOIN ( SELECT @rank_cnt := 0, @prev_score := NULL) i
       ORDER BY b.score DESC, b.id DESC

if I run above SQL, I get following result,

But I want to know from here, specific user's rank info only.

If I wrote WHERE name = 'sim' before ORDER BY, his rank become 1. I expect here '4' as result.

How should I revise?

Thanks much.

Answer 1

SET @rank_cnt := 0;
SET @prev_score := NULL;
SELECT * FROM (
   SELECT b.id
    , b.name
    , @rank_cnt := IF(@prev_score = b.score,@rank_cnt,@rank_cnt+1) AS rank
    , @prev_score := b.score AS score
   FROM BBR b
   ORDER BY b.score DESC, b.id DESC
) AS subQ
WHERE subQ.name = "sim";

If you are using the same connection, you shouldn't need that bogus "JOIN" to initialize your session variables.