Attach instead of correlated subquery

Question

CREATE TABLE BlogPosts ( PostID INT PRIMARY KEY not null, PostTitle NVARCHAR , BlogID int, TotalComments int ) 

Is it possible to simplify this query with any join instead of a correlated subquery?

 SELECT TOP 5 * FROM BlogPosts as t0 WHERE t0.PostID = (SELECT TOP 1 t1.PostID FROM BlogPosts as t1 WHERE t0.BlogID = t1.BlogID ORDER BY t1.TotalComments DESC) 

I need 5 posts with max TotalComments from different blogs.

UPD SQL Server, but I would prefer standard SQL

Answer 1

If I understand correctly, postid is unique, so this should help

EDIT:

Ok try this then

 DECLARE @BlogPosts TABLE ( PostID INT PRIMARY KEY not null, PostTitle NVARCHAR , BlogID int, TotalComments int ) INSERT INTO @BlogPosts (PostID,PostTitle,BlogID,TotalComments) SELECT 1, 'A', 1, 3 INSERT INTO @BlogPosts (PostID,PostTitle,BlogID,TotalComments) SELECT 2, 'B', 1, 4 INSERT INTO @BlogPosts (PostID,PostTitle,BlogID,TotalComments) SELECT 3, 'C', 2, 5 INSERT INTO @BlogPosts (PostID,PostTitle,BlogID,TotalComments) SELECT 4, 'D', 2, 6 INSERT INTO @BlogPosts (PostID,PostTitle,BlogID,TotalComments) SELECT 5, 'E', 2, 7 INSERT INTO @BlogPosts (PostID,PostTitle,BlogID,TotalComments) SELECT 6, 'F', 1, 8 INSERT INTO @BlogPosts (PostID,PostTitle,BlogID,TotalComments) SELECT 7, 'G', 3, 9 INSERT INTO @BlogPosts (PostID,PostTitle,BlogID,TotalComments) SELECT 8, 'H', 4, 10 INSERT INTO @BlogPosts (PostID,PostTitle,BlogID,TotalComments) SELECT 9, 'I', 5, 11 INSERT INTO @BlogPosts (PostID,PostTitle,BlogID,TotalComments) SELECT 10, 'J', 6, 5 SELECT TOP 5 * FROM @BlogPosts bp INNER JOIN ( SELECT BlogID, MAX(TotalComments) MaxComments FROM @BlogPosts GROUP BY BlogID ) maxCommentsPerBlog ON bp.BlogID = maxCommentsPerBlog.BlogID AND bp.TotalComments = maxCommentsPerBlog.MaxComments ORDER BY bp.TotalComments DESC 

You may have several maximum blog-totalComments combinations.

Answer 2

This will give you the best blog post:

 SELECT * FROM ( SELECT *, Ranking = ROW_NUMBER() OVER (PARTITION BY BlogID ORDER BY TotalComments DESC) FROM BlogPosts ) a WHERE Ranking = 1 

As an alternative:

 SELECT b.* FROM ( SELECT DISTINCT BlogID FROM BlogPosts ) a CROSS APPLY ( SELECT TOP 1 b.* FROM BlogPosts b WHERE a.BlogID = b.BlogID ORDER BY b.TotalComments DESC ) b 

Is this what you are looking for?

Answer 3

Why are joins and subqueries needed? Why can't you write

 SELECT TOP 5 * FROM @BlogPosts bp order by TotalComments desc;