0

Looking around the site, most questions regarding to Ranks in a highscore table assumes that you will be looking at the entire or the top of the table.

In a lot of examples on this site, the rank is found by ordering the items by score and then counting the rows from the top of the set, or counting the items as they are retrived. Like this

score  name     rank
1000   test345  1
999    test980  2
950    test234  3
833    test291  4
760    test573  5
731    test981  6

In my situation, I need to look at only a portion of the scores, which may not be at the top of the table, for instance, maybe halfway though the leaderboard:

scores  name     rank
500     test451  43
433     test768  44
425     test120  45

where the user is only shown the scores around his. The part of the leader board the user is looking at above, isn't at the top of the leader board, so I can't count the rows in the returned scores to determine their rank.

How can I determine the rank of user in a leader board at and arbitrary position efficiently, amusing there a lot of entries.

Also this is my first foray into sql and php. I might not be using the correct terminology.

Qdot543
  • 51
  • 1
  • 8
  • 1
    To only get a portion you would use `LIMIT` with an offset and you can also use `ORDER BY rank DESC` to sort your results depending on `rank` i.e: `SELECT * FROM ORDER BY rank DESC LIMIT 0, 3`
     – Cyclonecode Feb 10 '15 at 07:15
  • Are you asking "how do I calculate rank, based on score without looking at the entire table"? – G B Feb 10 '15 at 07:16
  • 1
    Right now your question is very unclear, you need to be more specific. – Cyclonecode Feb 10 '15 at 07:20
  • 1
    What you are showing us is the desired result? So what does the table look like? As to terminology: You are talking of a *table*, not the *database*. The database is *all* tables. – Thorsten Kettner Feb 10 '15 at 07:21

1 Answers1

0

I'm not really sure what you are trying to do. You can limit you result using a LIMIT clause like this:

SELECT * FROM <table> LIMIT 0, 3

Which will only return the first 3 records.

To order the result based on the rank field you would use an ORDER BY clause:

SELECT * FROM <table> ORDER BY rank DESC LIMIT 0, 3

The above query will return 3 records order by rank in descending order.

If you like to calculate the rank based on the scores column this would work:

SELECT scores, 
       name, 
       FIND_IN_SET(scores, (SELECT GROUP_CONCAT(scores ORDER BY scores DESC) 
       FROM <table>)) as rank 
FROM <table> ORDER BY rank DESC LIMIT 0, 3;

Running the above query against a table with only two columns scores and name:

+--------+---------+
| scores | name    |
+--------+---------+
|    500 | test451 |
|    433 | test768 |
|    425 | test120 |
|    300 | test001 |
|    250 | test002 |
|    200 | test003 |
+--------+---------+

Would yield the following result:

+--------+---------+------+
| scores | name    | rank |
+--------+---------+------+
|    500 | test451 |    1 |
|    433 | test768 |    2 |
|    425 | test120 |    3 |
+--------+---------+------+

The GROUP_CONCAT() maximum length is depending on the group_concat_max_len system variable, so for a large table this needs to be changed and I'm not sure this would be the best approach.

Notice that you could/should add indexes to your table to speed things up:

ALTER <table> ADD INDEX `idx_scores` (`scores`);
Cyclonecode
  • 29,115
  • 11
  • 72
  • 93
  • Thank you for trying to answer my question. I've made a few edits to my post to be clearer. Unfortunately you misinterpreted my question (understandably). What I want to do is look up the scores at certain part of the leaderboard where the users score is, it may be in the middle or at the bottom, but still determine his rank on the entire leaderboard. The solution you proved only works if the leaderbaords are small and if you are retrieving the entire leader board. – Qdot543 Feb 10 '15 at 09:08
  • @Qdot543 - Of course you would have to look at the whole table in order to do this. My example works even with a large table and you don't have to retrieve the entire leader board? Either you can set a limit with an offset as I showed above or you could add some condition to a `WHERE` clause. The important thing is that you'll need to use an index on your `scores` column. – Cyclonecode Feb 10 '15 at 09:11
  • @Qdot543 - Another approach would be to use some temporary table to speed things up. Otherwise you will need to sort the entire table. Have a look at this post: http://stackoverflow.com/questions/4845290/calculating-rank-in-php-mysql – Cyclonecode Feb 10 '15 at 09:27
  • Thanks for the post. I'm testing with a table of 100000 items, and the rank is starts becoming 0 after 146 items. Mind you i'm using xampp – Qdot543 Feb 10 '15 at 09:59
  • @Qdot543 - You're right. The reason for this is the `GROUP_CONCAT()` call. You could change the length for this `group_concat_max_len`, but I'm not sure this would be the best approach. I think you should stick with a temporary table that you will update only when needed i.e calculate the rank for each user and then just query this table based on the users id or similar. – Cyclonecode Feb 10 '15 at 11:24