Is it possible to get the number of a specific row when sorting in MySQL

Question

MySQL

Example: I have N users with exp from 0 to M.
Users are sorted in descending order, from M to 0. Is it possible to get the user number with id in this list?
Now I use REDIS.ZSET for this, but it would be interesting to know the solution on MySQL.

Answer 1

--- karma of user n.
SET @user_exp = (SELECT exp FROM users where id = {n});

SELECT count(*) FROM users
    WHERE exp > @user_exp OR (exp = @user_exp AND user.id < {n});

The second condition in OR is needed in case two users have the same karma (then you need to sort by both exp and id so that users with the same karma do not jump from place to place even without changing karma). If this can be neglected, then the condition can be neglected.
But it's better to update the special field every 5 minutes with a script.

Answer 2

SET @rn := 0;
SELECT * FROM (
SELECT @rn := @rn+1 AS id, exp
FROM users
ORDER BY users DESC
) WHERE id = {n};
something like that

Answer 3

SELECT u1.*, COUNT(u2.id)+1 AS rating FROM users u1, users u2
WHERE u1.id = ? AND u2.exp > u1.exp

(The idea is to count the number of users with more experience than this one)

Answer 4

SET @rows_count = NULL;
SELECT name, @rows_count := IFNULL(@rows_count, 0) + 1 FROM city LIMIT 10;