从有序查询中查询Mysql

Question

I've done some research and ended up with this query.

SET @row_num = 0;
SELECT
   @row_num := @row_num + 1 as row_number
  ,id
  ,maxPoints
FROM users
ORDER BY maxPoints

...that gives me indexes of table ordered by maxPoints. And now comes the query for smarter people...

How do I get the row_number WHERE id = $i AND ... (other parameters) from this result.

Is it possible to do it by one composed query or do I need to split those queries on php side? I'm not able to put it up syntactically.

Answer 1

Just put it in a subquery and select from that

select row_number
from
(
    SELECT id, @row_num := @row_num + 1 as row_number
    FROM users
    CROSS JOIN (select @row_num := 0) r
    ORDER BY maxPoints
) tmp
where id = $id

BTW you can init the @row_num variable with a subquery too, like I did in my query