October 29, 2004
Little SQL help please?
Posted by bopuc at October 29, 2004 12:04 AM
Hoping one of you can help me out with this...
Say I wanted to have a list of the 10 most frequent commenters on this weblog, sorted by number of comments each has left, and selected by the commenter's email address. Can I do this with just an SQL query or would I need some PHP logic as well?
And, of course what would either look like roughly? I'm thinking SELECT DISTINCT and a COUNT in there somewhere?
:D
Thanks in advance.. cough...
Comments
From memory:
SELECT DISTINCT comment_author,comment_email,COUNT(*) AS nb_comments
FROM mt_comment
GROUP BY comment_author,comment_email
ORDER BY nb_comments DESC
LIMIT 0,10
Notes:
1. If your commenters change email or author name their comments won't be grouped together.
2. Be careful what you do with your commenters' email. (of course)
:D
Posted by: Patrick at October 29, 2004 12:19 AM
Oops actually no need for DISTINCT since we're doing a GROUP BY, so get rid of it.
Posted by: Patrick at October 29, 2004 12:20 AM
Wow!! 15 minutes!!!
Merci Patrick!!!
:D
Posted by: Boris Anthony at October 29, 2004 12:29 AM