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?

:D

Thanks in advance.. cough...

Comments
Fri, Oct 29, 2004, @ 0:19
1- Patrick

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


Fri, Oct 29, 2004, @ 0:20
2- Patrick

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


Fri, Oct 29, 2004, @ 0:29
3- Boris Anthony

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

:D