Top 5 comments from a specific post. How to write my SQL
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 开发者_如何学JAVAneed 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
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
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
精彩评论