SQL query: select five most commented posts from different blogs

I have a following tables Blogs(id) and Posts(id, blog_id, comment_count) I need to select five most commented posts from different blogs. It’s quite easy to do with a function, but is it possible to do with some kind of basic SQL?

Here’s the query in SQL Server dialect

select top 5 top_post.* from Blogs b
cross apply
   (select top 1 * from Posts p 
    where p.blog_id = b.id 
    order by p.comment_count) top_post 
order by top_post.comment_count

Answer

SELECT b.*, c.num_comments
FROM 
(
   SELECT TOP 5 blog_id, SUM(comment_count) as num_comments 
   FROM posts GROUP BY blog_id ORDER BY SUM(comment_count) DESC
)c
INNER JOIN Blogs b ON (b.id = c.blog_id)

UPDATE Hopefully it’s what you need. It’s not very fast though.

SELECT b.*, c.comment_count
FROM
(SELECT blog_id, comment_count , 
 ROW_NUMBER() OVER(PARTITION by blog_id ORDER BY comment_count DESC) as rnum
 FROM posts  
)c
INNER JOIN Blogs b ON (b.id = c.blog_id)
WHERE c.rnum <=5;