Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE BlogPosts
- (
- PostID INT PRIMARY KEY not null,
- PostTitle NVARCHAR ,
- BlogID int,
- TotalComments int
- )
- 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)
- 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
- SELECT * FROM (
- SELECT *, Ranking = ROW_NUMBER() OVER (PARTITION BY BlogID ORDER BY TotalComments DESC)
- FROM BlogPosts
- ) a
- WHERE Ranking = 1
- 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
Add Comment
Please, Sign In to add comment