October 29, 2004 00:04 | WebTech

Little SQL help please?

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?


Thanks in advance.. cough...


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

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)


Oops actually no need for DISTINCT since we're doing a GROUP BY, so get rid of it.

Wow!! 15 minutes!!!
Merci Patrick!!!