Take specific number of records per each supplied Id-CodePudding

How would one write a query to take 5 records per each supplied ID? Let's say I have 5 comments, and I want to load 5 replies, and a total reply count for each comment.

Something like

select ReplyId, ReplyContent, COUNT(*) TotalCount 
from Replies R 
where R.CommentId in (1,2,3,4,5)
Order By R.PublishDate Desc
TAKE 5, for each id

Desired result would be a data set with a maximum of 50 records in this case. A 5 or less replies for each comment, and total number of replies for each comment.

CodePudding user response：

You can use ROW_NUMBER for example:

SELECT ReplyId, ReplyContent, TotalCount
FROM
(
SELECT 
  CommentId,
  ReplyId, 
  ReplyContent, 
  COUNT(*) OVER(PARTITION BY R.CommentId) TotalCount,
  ROW_NUMBER() OVER(PARTITION BY R.CommentId ORDER BY R.PublishDate DESC) row_n
FROM 
  Replies R where R.CommentId in (1,2,3,4,5)
) t
WHERE t.row_n <= 5