I have ranking system, which is result based on points.
User | Points
1 | 100
2 | 197
3 | 123
4 | 174
5 | 98
...
197 | 21
198 | 110
199 | 154
Assuming that my user-id is 197, so first I want to know my rank based or points, highest points at first, lowest at last, so here assume that my rank (user-id = 197) is #150
After getting my rank (if my rank is not in top 100 list) then I want to get list of 100 users where in, I need to get 1st to 50th rank users + 125th to 175th rank users so I can get my rank in this list too, but for list ranks will be actual ranks in results
User | Points | Rank
18 | 199 | 1
22 | 198 | 2
31 | 180 | 3
19 | 174 | 4
51 | 168 | 5
+
17 | 22 | 149
197 | 21 | 150
199 | 14 | 151
I have app in PHP, so what is best and efficient way to achieve this result set?
The overall query for returning scores with their rank would be (or should I say 'could be', as it works but might not be the most elegant solution):
SET @count = 0;
SELECT
user,
points,
rank
FROM(
SELECT user,
points,
@count := @count + 1 'rank'
FROM scores
ORDER BY points DESC
) as ranks;
So if you wanted to know the rank of a specific user, you'd add a where clause at the end, of:
WHERE user = 197;
When it comes to the second part, especially with the formatting, I think that's best done in PHP. You could run the above to obtain your 'rank', then do something like this (note: I'm just pseudo quoting PHP here, rather than scripting an answer):
$myRank = [result of above query];
if($myRank > 100) {
// retrieve first 50 results and display in query 1
echo "..."; // or in a <td>, etc.
// retrieve results x to y and display in query2, loop through a table etc.
}
else {
// retrieve the first 100 if your score is in that range
}
The SQL for the first 50 results would be
SET @count = 0;
SELECT
user,
points,
rank
FROM(
SELECT user,
points,
@count := @count + 1 'rank'
FROM scores
ORDER BY points DESC
) as ranks
LIMIT 0,50;
and then you'd have to use the output of our theoretical $myRank in php to obtain the next set of results. For example, if you wanted the next 50 after your rank, you'd change the last line to:
LIMIT $myRank,$upper;
where upper would be, in php:
$upper = $myRank+50;
You'd obviously need to figure out exactly what you want to achieve in terms of different scenarios before you could really tie down a complete solution, but hopefully this is of some help.