Get current rank using mysql

问题:

i've got this query to get the rank of rows, order by some field

SET @rank=0;
SELECT @rank:=@rank+1 AS rank, id, ammopacks
  FROM users
  GROUP BY id
  ORDER BY ammopacks DESC;

How about getting the rank of "x" id, without ranking all them before? I don't want to know all users rank, just only one id.

Is it possible?

Thanks in advance

回答1:

You can do this with a subquery:

select count(*) as rank
from users u
where u.ammopacks >= (select ammopacks from users u2 where u2.id = x)

This doesn't do exactly the same thing. This will do a real ranking, where users with the same value of ammopacks will have the same rank. The original would give different users different sequential values in this case.

To get this effect, you can do:

select count(*) as rank
from users u
where u.ammopacks > (select ammopacks from users u2 where u2.id = x) or
      (u.ammopacks = (select ammopacks from users u2 where u2.id = x) and
       u.id <= x
      )