Top 5 comments from a specific post. How to write my SQL

Question

I want to show only the top 5 comments for a specific post (Like the post on facebook where people cant comment them).

gbn was kind enough to help figure out that issue by doing the following:

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

But since I'm no sql grand master I would need your help to put that into my real sql statement.

SELECT *
FROM
memberactions INNER JOIN
actions ON memberactions.actionid = actions.id INNER JOIN
members ON memberactions.memberid = members.id LEFT OUTER JOIN
members members_2 INNER JOIN
actioncomments ON members_2.id = actioncomments.memberid INNER JOIN
comments ON actioncomments.commentid = comments.id ON actions.id = actioncomments.actionid

So my question is could you rearrange my sql to put the OUTER APPLY in my real sql statement.

Assuming that my table comments reprensent tblComment and that actioncomments represent the table tblPost

Answer 1

There does not seem to be a need for the member_2 table at all, but this is a faithful representation of what you had (preserving member_2)

SELECT *
FROM memberactions
INNER JOIN actions
    ON memberactions.actionid = actions.id
INNER JOIN members
    ON memberactions.memberid = members.id
OUTER APPLY (
    select top(5) *
    FROM actioncomments
    inner join comments ON actioncomments.commentid = comments.id
    inner join members members_2 ON members_2.id = actioncomments.memberid
    WHERE actions.id = actioncomments.actionid
    order by comments.id desc) comments

Answer 2

SELECT *
FROM
memberactions 
INNER JOIN actions 
ON memberactions.actionid = actions.id 
INNER JOIN members 
ON memberactions.memberid = members.id 
LEFT OUTER JOIN members members_2 
    INNER JOIN actioncomments 
    ON members_2.id = actioncomments.memberid 
    OUTER APPLY  

    (SELECT TOP 5 * FROM comments C  
          WHERE actioncomments.commentid = comments.id 
      ORDER BY something)


ON actions.id = actioncomments.actioni

still don't know what you want to order by