MySQL ranking with pagination

Question

I have an SQL query that looks like:

SELECT member_id, Count(*) AS '# of Rounds'
FROM   score,cup_point
WHERE  session_id =?
       AND tour_id =?
       AND cup_point_id = `cup_point`.id
GROUP  BY member_id
ORDER  BY Sum(points) DESC
LIMIT  50 offset 0 

How do I include the ranking in my query so that:

1. I get a return column with a number based on the score i.e. SUM(points), so the highest score would have a ranking of 1, etc.
2. When paginating using the offset and limit, or even when I filter by member id, I still get the correct ranking for that member (member_id) ?

Thanks.

Answer 1

Perhaps with a subquery and a running counter

SET @x = 0;
SELECT member_id,NumRounds AS '# of Rounds',@x:=(@x+1) Rank FROM
(
SELECT member_id, Count(*) AS NumRounds
FROM   score,cup_point
WHERE  session_id =?
       AND tour_id =?
       AND cup_point_id = `cup_point`.id
GROUP  BY member_id
ORDER  BY Sum(points) DESC
LIMIT  50 offset 0 
) A;

I have addressed something like this in the DBA StackExchange on Jun 08, 2012

Give it a Try !!!