Join instead of correlated subquery

Question

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

May this query be simplified with any Join instead of 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 might have multiple max blog-totalComments combinations though.

Answer 2

This will give you the top post per blog:

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

Alternatively:

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 that what you're looking for?