Menu
Using PHP and MySQL, I want to query a table of postings my users have made to find the person who has posted the most entries.
What would be the correct query for this?
Sample table structure:
[id] [UserID] 1 johnnietheblack 2 johnnietheblack 3 dannyrottenegg 4 marywhite 5 marywhite 6 johnnietheblack
I would like to see that "johnnietheblack" is the top poster, "marywhite" is second to best, and "dannyrottenegg" has the least
448
You need to group by the interesting column and for each value, select the value itself and the number of rows in which it appears. Then it's a matter of sorting (to put the most common value first) and limiting the results to only one row.
You can use ORDER BY clause or aggregate function MAX() to select the maximum value.
The SQL intersect operator allows us to get common values between two tables or views. The following graphic shows what the intersect does. The set theory clearly explains what an intersect does. In mathematics, the intersection of A and B (A ∩ B) is the set that contains all elements of A that also belong to B.
Something like:
SELECT COUNT(*) AS `Rows`, UserID
FROM `postings`
GROUP BY UserID
ORDER BY `Rows` DESC
LIMIT 1
This gets the number of rows posted by a particular ID, then sorts though the count to find the highest value, outputting it, and the ID of the person. You'll need to replace the 'UserID' and 'postings' with the appropriate column and field though.
130
I believe this should work...
SELECT user_id, COUNT(*) FROM postings ORDER BY COUNT(*) GROUP BY user_id LIMIT 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
