I apologize in advance if this explanation is not 100% clear.
I am trying to list users in order based on their ratings (and then ordered by their userids). I am actually trying to paginate the results because there could potentially be a large number of results.
I have two tables:
There are other columns but these are the relevant ones here.
Every user can submit a rating of a group using the following values: -1, 0, or 1 which is recorded in "rating". Each group's net rating is evaluated by getting the net summation of "status" ratings that correspond to a given group.
I have this basic query:
SELECT groupid, userid, SUM(rating)
FROM A LEFT JOIN B ON A.userid = B.userid
GROUP BY A.userid
ORDER BY SUM(rating) DESC, A.userid ASC
However, I'd like to paginate so I need to be able to choose results based on the last userid that was previously displayed.
Normally, it would be easy to filter results based on any results that has userid greater than the last userid displayed. However, since I am ordering the results based on SUM(rating), there is really no sequential ordering system.
Is there a good way to filter my results for pagination purposes even though my userid's in the results are not ordered sequentially?
Have you tried using limit with an offset?