SQL Join and Count can't GROUP BY correctly?

Question

Hi,

So let's say I want to select the ID of all my blog posts and then a count of the comments associated with that blog post, how do I use GROUP BY or ORDER BY so that the returned list is in order of number of comments per post?

I have this query which returns the data but not in the order I want? Changing the group by makes no difference:

SELECT p.ID, count(c.comment_ID) 
FROM wp_posts p, wp_comments c 
WHERE p.ID = c.comment_post_ID 
GROUP BY c.comment_post_ID;

Thanks

Answer 1

SELECT p.ID, count(c.comment_ID) AS [count]
FROM wp_posts p, wp_comments c 
WHERE p.ID = c.comment_post_ID 
GROUP BY c.comment_post_ID;
ORDER BY [count] DESC

Answer 2

I'm not familiar with pre-SQL92 syntax, so I'll express it in a way that I'm familiar with:

SELECT c.comment_post_ID, COUNT(c.comment_ID)
FROM wp_comments c
GROUP BY c.comment_post_ID
ORDER BY COUNT(c.comment_ID) -- ASC or DESC

What database engine are you using? In SQL Server, at least, there's no need for a join unless you're pulling more data from the posts table. With a join:

SELECT p.ID, COUNT(c.comment_ID)
FROM wp_posts p
JOIN wp_comments c ON c.comment_post_ID = p.ID
GROUP BY p.ID
ORDER BY COUNT(c.comment_ID)