TSQL left join and only last row from right

Question

I'm writing sql query to get post and only last comment of this post(if exists). But I can't find a way to limit only 1 row for right column in left join.

Here is sample of this query.

SELECT post.id, post.title,comment.id,comment.message from post left outer join comment on post.id=comment.post_id 

If post has 3 comments I get 3 rows with this post, but I want only 1 row with last comment(ordered by date).

Can somebody help me with this query?

Answer 1

SELECT  post.id, post.title, comment.id, comment.message FROM    post OUTER APPLY         (         SELECT  TOP 1 *         FROM    comment с         WHERE   c.post_id = post.id         ORDER BY                 date DESC         ) comment 

or

SELECT  * FROM    (         SELECT  post.id, post.title, comment.id, comment.message,                 ROW_NUMBER() OVER (PARTITION BY post.id ORDER BY comment.date DESC) AS rn         FROM    post         LEFT JOIN                 comment         ON      comment.post_id = post.id         ) q WHERE   rn = 1 

The former is more efficient for few posts with many comments in each; the latter is more efficient for many posts with few comments in each.

Answer 2

Subquery:

SELECT p.id, p.title, c.id, c.message FROM post p LEFT join comment c ON c.post_id = p.id AND c.id =                   (SELECT MAX(c.id) FROM comment c2 WHERE c2.post_id = p.id)