Max occurences of a given value in a table

Question

I have a table (pretty big one) with lots of columns, two of them being "post" and "user". For a given "post", I want to know which "user" posted the most.

I was first thinking about getting all the entries WHERE (post='wanted_post') and then throw a PHP hack to find which "user" value I get the most, but given the large size of my table, and my poor knowledge of MySQL subtle calls, I am looking for a pure-MySQL way to get this value (the "user" id that posted the most on a given "post", basically).

Is it possible ? Or should I fall back on the hybrid SQL-PHP solution ?

Thanks, Cystack

Answer 1

It sounds like this is what you want... am I missing something?

SELECT user
FROM myTable
WHERE post='wanted_post'
GROUP BY user
ORDER BY COUNT(*) DESC
LIMIT 1;

EDIT: Explanation of what this query does:

Hopefully the first three lines make sense to anyone familiar with SQL. It's the last three lines that do the fun stuff.

1. GROUP BY user -- This collapses rows with identical values in the user column. If this was the last line in the query, we might expect output something like this:

   +-------+
   | user  |
   +-------+
   | bob   |
   | alice |
   | joe   |
   

2. ORDER BY COUNT(*) DESC -- COUNT(*) is an aggregate function, that works along with the previous GROUP BY clause. It tallies all of the rows that are "collapsed" by the GROUP BY for each user. It might be easier to understand what it's doing with a slightly modified statement, and it's potential output:

   SELECT user,COUNT(*)
   FROM myTable
   WHERE post='wanted_post'
   GROUP BY user;
   
   +-------+-------+
   | user  | count |
   +-------+-------+
   | bob   |     3 |
   | alice |     1 |
   | joe   |     8 |
   

   This is showing the number of posts per user.

   However, it's not strictly necessary to actually output the value of an aggregate function in this case--we can just use it for the ordering, and never actually output the data. (Of course if you want to know how many posts your top-poster posted, maybe you do want to include it in your output, as well.)

   The DESC keyword tells the database to sort in descending order, rather than the default of ascending order.

   Naturally, the sorted output would look something like this (assuming we leave the COUNT(*) in the SELECT list):

   +-------+-------+
   | user  | count |
   +-------+-------+
   | joe   |     8 |
   | bob   |     3 |
   | alice |     1 |
   

3. LIMIT 1 -- This is probably the easiest to understand, as it just limits how many rows are returned. Since we're sorting the list from most-posts to fewest-posts, and we only want the top poster, we just need the first result. If you wanted the top 3 posters, you might instead use LIMIT 3.