MySQL and GROUP BY grief

[ThomThom]

I'm trying to make a statistic page for my blog.

I want to list all blog entires which has comments related to it and display the number of comments for each blog entry. I also want the entry with the last activity at the top.

Here's the SQL query I got atm: (this is simplified and only return the id of the blog entry (parent_id).)

SELECT parent_id, date, COUNT(*) FROM blog_comments GROUP BY parent_id ORDER BY date DESC;

With this, I do get a list of the blog entries and the number of comments. However it's not ordered by the last activity. Whenever I make a comment on one of the blog entries the list doesn't change.

It seems to me that the ORDER BY is applied after the items has been grouped, which means that the date I get for each group isn't the date for the last comment.

Can anyone enlighten me?

[Lord Majestic]

When you GROUP BY you need to group by all non-aggregate columns - some databases will warn you about that, and some won't - results will be not what you expect though.

Your problem is that you are not grouping by date. To get last activity you need to use MAX(date) - this would make that field aggregate and it should work.

[ThomThom]

You mean

SELECT parent_id, MAX(date), COUNT(*) FROM blog_comments GROUP BY parent_id ORDER BY date DESC;

?
That didn't work.

What did work was doing a subquery where I order the list first. But I'm not sure this is the best way to do it.

SELECT parent_id, date, COUNT(*) FROM (SELECT * FROM blog_comments ORDER BY date DESC) AS t1 GROUP BY parent_id ORDER BY date DESC;

[Lord Majestic]

When you say it did not work, what output did you get? That query should give ordered list by last date for each of the parent_id's and in addition calculating number of rows per parent_id.

[ThomThom]

I got it working now.

SELECT parent_id, MAX(date) AS d, COUNT(*) FROM blog_comments GROUP BY parent_id ORDER BY d DESC;

I forgot that when I added MAX(date) I couldn't order by date. So I aliased it and now it's working perfectly. Thanks!

[Lord Majestic]

ah, of course you had to order it by aggregate, my mistake - forgot about that - normally good database (like SQL Server) would warn about that :)