SQL grouping - Show only 5 comments per post

Question

To make thing easier to understand lets take the following example: I want to show only the top 5 comments for a specific post (Like the post on facebook where people cant comment them). How could I do this in SQL if you take this query

select tblPost.id,tblComment.id
from tblComment,tblPost
where tblPost.id = tblComment.postid

if I try select top 5 tblComment.id... it will return only 5 results and not all the post with only a maximum of 5 comment per post.

Answer 1

select
   *
FROM
   tblPost P
   OUTER APPLY
   (SELECT TOP 5 * FROM tblComment C  
      WHERE P.id = C.postid
      ORDER BY something) inline

Answer 2

Try using ROW_NUMBER, assuming you are using a database that supports it:

SELECT id, postid
FROM tblComment
(
    SELECT 
        id,
        postid,
        ROW_NUMBER() OVER (PARTITION BY postid ORDER BY id DESC) AS rn
    FROM tblComment
) AS T1
WHERE rn <= 5