CODEWITHSUNDEEP
mysqlsqldatabasesorting
user3596335
user3596335

Reputation:

mySql rank sorting from-to

I got a table like this:

users:

   | username |              statistics              |
   ----------- ---------------------------------------
0  | peter200 | { "gamesWon": 4, "gamesPlayed" : 4}  | 
1  | eminem33 | { "gamesWon": 7, "gamesPlayed" : 20} |

Note: (statistics = "JSON")

And I'd like to create a rank-list.

So the user with the highest number in statistics. gamesWon gets rank numero uno 1 and so on.

What I've got so far is something like this (what is exactly working like I've hoped):

SELECT username, statistics, @rank := @rank + 1 AS rank 
FROM users, (SELECT @rank := 0) r 
WHERE JSON_EXTRACT(statistics, '$.gamesWon') 
ORDER BY JSON_EXTRACT(statistics, '$.gamesWon') DESC

So to my question: Now I'd like to update the query from above to just get the information in an specific rank-index (lets say from rank 2 to rank 10).

Adding AND rank > 2 AND rank < 10 to the WHERE clause does not seems to be a working solution. So any help would be really appreciated.

Upvotes: 2

Views: 40

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You don't actually need the subquery. You can use limit with offset:

SELECT username, statistics, @rank := @rank + 1 AS rank 
FROM users CROSS JOIN (SELECT @rank := 0) r 
WHERE JSON_EXTRACT(statistics, '$.gamesWon') 
ORDER BY JSON_EXTRACT(statistics, '$.gamesWon') DESC
LIMIT 1, 9

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175646

You need to wrap it with subquery:

SELECT *
FROM (SELECT username, statistics, @rank := @rank + 1 AS rank 
      FROM users, (SELECT @rank := 0) r 
      WHERE JSON_EXTRACT(statistics, '$.gamesWon')
      ORDER BY JSON_EXTRACT(statistics, '$.gamesWon') DESC
     ) s
WHERE  rank > 2 AND rank < 10

Upvotes: 1

Related Questions