-1

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Robert
  • 3,353
  • 4
  • 32
  • 50
  • Please format your code so it's easy to read, even if it's pseudocode. A long line that requires scrolling isn't very readable – Panagiotis Kanavos Oct 21 '21 at 08:11
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Charlieface Oct 21 '21 at 09:52

1 Answers1

2

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
Robert
  • 3,353
  • 4
  • 32
  • 50
Joao Leal
  • 5,533
  • 1
  • 13
  • 23