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
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;