asuidhgpauieytpioawet

Ok so I’m try­ing to do a cus­tom SQL query on the wp_comments data­base table in order to cre­ate a list of com­menters who’ve either com­mented more than once using the same email address or who’ve com­mented in the about page, and who have at least one asso­ci­ated URL, pro­duc­ing a kind of auto-generated “friends” list, with links, and with the most fre­quent com­menters at the top. You can see the really crappy unfor­mat­ted ver­sion I’ve man­aged beneath the image map in on the right side of the footer.

So far this is what I’ve got:

This does almost pre­cisely what I want, but because GROUP BY col­lapses an entire group of rows with the same comment_author_email, leav­ing just the first row, the comment_author_url retrieved ends up being the URL the com­ment author entered in their first com­ment, not their last, because the whole table by default is sorted by comment_ID ascend­ing. Adding comment_ID DESC to the ORDER BY para­me­ter just sorts after everything’s already been grouped/collapsed.

Point is I am not nearly famil­iar enough with SQL to fig­ure this out yet. I think I need to do a sub­query or a self-join, but as far as I can tell all the meth­ods I’ve read for doing that are in SQL syn­tax, not MySQL syn­tax, which seem­ingly is sig­nif­i­cantly dif­fer­ent? I’ll prob­a­bly even need to do some more com­plex stuff to account for, say, peo­ple who com­ment using mul­ti­ple email addresses and mul­ti­ple URLs, but who are iden­ti­fi­ably the same per­son through over­lap in both.

In the process, any­way, I think I’ve famil­iar­ized myself enough with $wpdb, and re-familiarized myself enough with SQL, that doing a “recent com­ments” sec­tion won’t be dif­fi­cult at all. They have plu­g­ins that do that but I hate rely­ing on things whose devel­op­ment could stop at any moment, I’ve seen too many dead plu­g­ins ren­dered incom­pat­i­ble with recent Word­Press ver­sions to ever want to use them.