MySQL: Querying a leaderboard with ties

Question

I know this is a popular topic, but I still haven't found quite what I'm looking for. I'd like to query one table

BOOKS_READ
id
user_id
book_id

to format a leaderboard of users who have listed the most books as having been read. When a user reads a book, a record matching the book id and the user id gets logged into the books_read table.

Is it possible to rank the results of this query, starting at 1, and with consideration of ties?

SELECT user_id, COUNT(*) AS book_count 
FROM books_read 
GROUP BY user_id 
ORDER BY book_count DESC LIMIT 10

In the event of a tie, I would like to list an '=' sign in the results.

For example,

rank  user_id  book_count  
=1    30       121
=1    17       121
 2    101      119
=3    11       104
=3    91       104

Many thanks for any help! I don't mind using PHP to handle some of it, but I'm very interested in learning straight SQL solutions to these kinds of things :-)

Wouldn't 30 and 17 tie for 1st, 101 get 3rd, and 11 and 91 tie for 4th? — cdhowie, Aug 16 '11 at 00:21

score 2 · Answer 1 · answered Aug 16 '11 at 00:32

2

SELECT GROUP_CONCAT(user_id, book_count
FROM (
    SELECT user_id, COUNT(*) AS book_count 
    FROM books_read 
    GROUP BY user_id 
    ORDER BY book_count DESC
) AS T1
GROUP BY book_count
ORDER BY book_count

Giving you

user_id  book_count  
30,17       121
101         119
11,91       104

Which you can then use PHP to parse for ties.

<?php
$rank = 1;

while ($row = mysql_fetch_assoc($result)) {
    $users = explode(',', $row['user_id'];
    foreach ($users as $user) {
        echo 'Rank: ' . $rank . ' ' . $user . "\n;
    }
    $rank++;
}

?>

answered Aug 16 '11 at 00:32

Slashterix

1,036
1
10
17

Although this works, I think it's a bit cumbersome. You could just as easily use OP's original query and in php keep track of the score and have `$rank` decrement when the score is the same as the previous row, and increment by 2 afterwards. Thereby also eliminating the need for the `foreach` loop in the rows iteration. – Decent Dabbler Aug 16 '11 at 00:51

score 1 · Answer 2 · answered Aug 16 '11 at 00:47

What you want to do is count the number of people who have a better score than the record you're interested in, and add 1.

So, if you're the best player, zero people have a better score, so 0 + 1 = #1 ranking.

If five people have a better score, regardless of how many of them tied with each other, you're still 5 + 1 = 6.

The problem is that this is kind of an expensive query to do for every row you display. So you probably want to do it for the first row in your result set and then add one for everyone after that in your presentation layer (PHP). Be sure to take ties into consideration as you do this.

And the edge condition is that you don't know if the first row in your result set is tied with people ahead of "him". So you need to know how many people have the same score as the first person in your result set. (If you're starting at the top, this isn't a problem.)

MySQL: Querying a leaderboard with ties

2 Answers2

Linked