MySQL: querying leaderboards with bundles

Question

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

BOOKS_READ id user_id book_id 

to format the leaderboard of users who have listed most books as read. When a user reads a book, the record corresponding to the book ID and user ID is registered in the books_read table.

Is it possible to rank the results of this query, starting from 1, and taking into account the links?

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

In the case of communication, I would like to list the '=' sign in the results.

For instance,

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

Thanks so much for any help! I am not opposed to using PHP to handle some of them, but I am very interested in learning direct SQL solutions for such things :-)

Answer 1

 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 

That you can use PHP to parse links.

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

Answer 2

What you want to do is to count the number of people who have a better result than the record you are interested in and add 1.

So, if you are the best player, zero people have the best score, so the rating is 0 + 1 = # 1.

If five people have the best result, no matter how many of them are related to each other, you are still 5 + 1 = 6.

The problem is that this is a kind of expensive query for each row displayed. Therefore, you probably want to do this for the first line in your result set, and then add one for all after that in your presentation layer (PHP). Be sure to consider how you do it.

And the condition of the region is that you do not know if the first line of your result set is connected with people ahead of "it." Therefore, you need to know how many people have the same indicator as the first person in your result set. (If you start from above, this is not a problem.)