Because I like to “showcase” my top commenters, I made a little box for those who comment the most. It’s a “top 5 list” of the people who have commented the most. Just insert this EE code where you want it to show up:
{exp:query sql=”SELECT DISTINCT name, email, url, count( email ) AS count FROM `exp_comments` GROUP BY email ORDER BY count DESC LIMIT 0 , 5″}
<a href=”{url}” target=”_blank”>{name}</a> – {count} retorts<br />
{/exp:query}
That will give you the top commenters for all your weblogs with the commenter with the most comments on top. If you want to narrow down by weblog, then add a WHERE statement saying “WHERE weblog_id=#” where # is the weblog ID number. Therefore the query will read: “SELECT DISTINCT name, email, url, count( email ) AS count FROM `exp_comments` WHERE weblog_id=1 GROUP BY email ORDER BY count DESC LIMIT 0 , 5″