MySQL Forums
Forum List  »  Newbie

Re: How to rank MySQL results, based on different values?
Posted by: Shawn Green
Date: June 26, 2014 10:00AM

Hello Radical,

SQL is not a sequential language, it is a set-based language. Sets can be ordered but not walked in the forward or reverse direction unless you use a cursor
http://dev.mysql.com/doc/refman/5.6/en/cursors.html

Your problem has three parts to it.

1) generate a list of all scores in ascending or descending order and assign each a 'rank' value. This can be something you keep permanently in your data and update each time a player posts a high score (recommended) or you can create this list on demand every time you need to run this query (inefficient)

Having a permanent table makes determining your "top 10" much much easier, for example.

2) You need to segregate which users are online from those not online (easy to do with an INNER JOIN) and filter rows from your list of ranked scores.

3) You need to know N scores above and below your target score. There are a few ways to do this.


Here is one way you can do your query without creating and maintaining a permanent list of ranking values.

(get the first two values from your points table greater than N)
union all
(get the record for your target user)
UNION ALL
(get the next two values less than N)




SELECT * FROM (SELECT userd, points FROM score INNER JOIN settings on settings.userid = score.userid and settings.active=1 WHERE points >N order by points asc limit 2) ORDER BY points desc;
UNION ALL
SELECT userid FROM score WHERE userid = ... (the id of the user you are ranking)
UNION ALL
(SELECT userd, points FROM score INNER JOIN settings on settings.userid = score.userid and settings.active=1 WHERE points < N order by points desc limit 2)


Things that will help:
a) Keep your active users and your inactive users in separate tables. This allows you to avoid the JOIN every time you want to build a ranking.

b) Maintain a permanent and updated list of ranked users (it only needs to be two columns). This will help you to deal with ties much easier (my example code does not take ties into consideration but could be easily modified to do so.)

c) Realise that there are many ways to solve this problem. Keep looking and trying different methods until you find one you like that also meets your performance needs.

D) Learn to think in sets. Sometimes its easier to build a set then sequence it than it is to generate and splice ordered sets together (temporary tables are your friends)

Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer

Options: ReplyQuote


Subject
Written By
Posted
Re: How to rank MySQL results, based on different values?
June 26, 2014 10:00AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.