Some people would call it a blog, not me though. This is just a place where I keep non-private notes and ideas that someone might find useful. Feel free to leave your comments (if any) in the feedback section or via e-mail.
MySQL: Custom MAX() vs COUNT() query
As you might know, GROUP BY is required for all the fields that are not in the agregate function of an SQL query. Well, I spent a couple of hours trying to build a query that would count the number of entries for each ID (user comments) and return a list with the IDs and corresponding COUNT of the ones where COUNT is the maximum. So how do you combine COUNT and MAX together?
Maybe there's a slickier solution, but this is what I came up with after reading someone's tips:
SELECT idx, COUNT( * ) AS num_comments
LEFT JOIN members ON members.idx = comments.uid
GROUP BY idx
HAVING num_comments = (
SELECT COUNT( * ) AS num_comments_max
FROM comments AS c
GROUP BY c.uid
ORDER BY num_comments_max DESC
Let me know if there's a better approach, and I'll update this page.