SELECT user ranking on MySQL by an offset

Question

I'm trying to make a query in MySQL that returns me only 10 users from the table, but with a rank value that is the ORDER result of the xp column. Right now I have this:

SELECT id, xp, @curRank := @curRank + 1 AS rank
    FROM usuarios, (SELECT @curRank := 0) r
    ORDER BY xp DESC LIMIT 10;

It looks to be working perfectly when fetching the first 10 users.

+--------------------+------+------+
| id                 | xp   | rank |
+--------------------+------+------+
| 373901344995803138 | 5863 |    1 |
| 701198768049225770 | 5692 |    2 |
| 239203656405221376 | 4961 |    3 |
| 692489002942726154 | 4508 |    4 |
| 416988898628206593 | 3669 |    5 |
| 312003290378534912 | 3155 |    6 |
| 608344569381126167 | 3059 |    7 |
| 671949142473310238 | 3041 |    8 |
| 549743978191519744 | 2991 |    9 |
| 592440479577145383 | 2519 |   10 |
+--------------------+------+------+

But when I try to fetch for example LIMIT 10,10 to get the users between 11 and 20, although they are ordered, their global rank is incorrect because @curRank is not increasing for all the users before the offset.

+--------------------+------+------+
| id                 | xp   | rank |
+--------------------+------+------+
| 638196238436532234 | 1888 |    1 |
| 601269358349516833 | 1447 |    2 |
| 548357514497097743 | 1338 |    3 |
| 203591312031744000 | 1330 |    4 |
| 379034072519016469 | 1283 |    5 |
| 563804445654122497 | 1086 |    6 |
| 421296425981181952 | 1025 |    7 |
| 263816867100098560 |  850 |    8 |
| 631330775379214371 |  776 |    9 |
| 442529076511637504 |  702 |   10 |
+--------------------+------+------+

I don't know a way to make the global ranking work when using LIMIT.

Answer 1

In MySQL 8.0, just use window functions, as demonstrated by Gordon Linoff.

In earlier versions, you basically need a subquery to do what you want. I would recommend:

SELECT *
FROM (
    SELECT id, xp, @curRank := @curRank + 1 AS rank
    FROM (SELECT * FROM usuarios ORDER BY xp DESC) u
    CROSS JOIN (SELECT @curRank := 0) r
    ORDER BY xp DESC
) t
ORDER BY xp DESC 
LIMIT 10, 10;

The subquery ranks all users first, then you can safely filter in the outer query. Note that the query pre-orders the table by xp in a subquery first: this is safer (user variables are tricky in MySQL).

Actually, you don't even needLIMIT in the outer query; you can use a WHERE clause instead:

SELECT *
FROM (
    SELECT id, xp, @curRank := @curRank + 1 AS rank
    FROM (SELECT * FROM usuarios ORDER BY xp DESC) u
    CROSS JOIN (SELECT @curRank := 0) r
    ORDER BY xp DESC
) t
WHERE rank BETWEEN 11 AND 20
ORDER BY rank

Answer 2

Instead, use row_number():

SELECT id, xp, row_number() over (order by cp desc) as rnk
FROM usuarios
ORDER BY xp DESC
LIMIT 10;