MySQL Sorting By Top 10

Question

I am in the midst of creating a workout log for a team of mine. What I currently have is a MySQL query (written in PHP) that retrieves an array of user ids associated with posts made in the current week. The code is as follows:

mysql_query("SELECT u_id FROM posts WHERE id IN (SELECT p_id FROM post_groups WHERE g_id='" . $group['id']. "') AND date >= '" . $d_start . "' AND date <= '" . $d_end . "'");

To better explain this, say a user with u_id = 6 posts five workouts this week. The array generated by the above query should contain 6 a total of five times. This is done for every workout posted by users in the week - so I end up with a large array full of duplicate user ids.

What I am looking to do is, instead, return an array of size 10 that contains the u_ids that appear most frequently in the array above. So, if u_id = 6 appeared the most frequently in the above array, let's say 10 times, and u_id = 8 appeared the second most frequently, let's say 9 times, then the array that I wish to return would have a value of 6 at [0] and a value of 8 at [1], etc.

I am not sure how to trim down this array and sort it by most frequent occurrences of u_id, as my SQL skills are a little lacking at this moment. I was hoping someone would be able to shed some light on the ideal query that I am looking for. I appreciate your help!

Answer 1

It sounds like you want to use both GROUP BY, ORDER BY, and TOP.

GROUP BY will group your results by a particular field, in this case you want to be grouping by u_id.

GROUP BY u_id

ORDER BY lets you order your resultset. In this example you want to group by the COUNT() of how many rows each user has. We also use the DESC keyword to order them in descending order (highest to lowest).

ORDER BY COUNT(u_id) DESC

TOP lets you only get the top X results. In the example below only the top 10 rows will be returned.

TOP 10

So putting it all together, to group by user id, order by the count of each userid in descending order and to limit the results to 10:

SELECT u_id 
FROM posts 
WHERE id IN 
  (SELECT p_id 
   FROM post_groups 
   WHERE g_id='" . $group['id']. "') 
AND date >= '" . $d_start . "' 
AND date <= '" . $d_end . "'
GROUP BY u_id
ORDER BY COUNT(u_id) DESC
LIMIT 10

On a side note... you really shouldn't be using the mysql_* functions in new code. They are no longer maintained and are officially deprecated (meaning they'll be removed in a future version of PHP). Instead look into using prepared statements with either MySQLi or PDO.

There are plent of good examples of using PDO here, and here on using MySQLi.