Ok so I’m trying to do a custom SQL query on the wp_comments database table in order to create a list of commenters who’ve either commented more than once using the same email address or who’ve commented in the about page, and who have at least one associated URL, producing a kind of auto-generated “friends” list, with links, and with the most frequent commenters at the top. You can see the really crappy unformatted version I’ve managed beneath the image map in on the right side of the footer.

So far this is what I’ve got:

This does almost precisely what I want, but because GROUP BY collapses an entire group of rows with the same comment_author_email, leaving just the first row, the comment_author_url retrieved ends up being the URL the comment author entered in their first comment, not their last, because the whole table by default is sorted by comment_ID ascending. Adding comment_ID DESC to the ORDER BY parameter just sorts after everything’s already been grouped/collapsed.

Point is I am not nearly familiar enough with SQL to figure this out yet. I think I need to do a subquery or a self-join, but as far as I can tell all the methods I’ve read for doing that are in SQL syntax, not MySQL syntax, which seemingly is significantly different? I’ll probably even need to do some more complex stuff to account for, say, people who comment using multiple email addresses and multiple URLs, but who are identifiably the same person through overlap in both.

In the process, anyway, I think I’ve familiarized myself enough with $wpdb, and re-familiarized myself enough with SQL, that doing a “recent comments” section won’t be difficult at all. They have plugins that do that but I hate relying on things whose development could stop at any moment, I’ve seen too many dead plugins rendered incompatible with recent WordPress versions to ever want to use them.