TSQL left join and only last row from right

SELECT  post.id, post.title, comment.id, comment.messageFROM    postOUTER 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        ) qWHERE   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.

Subquery:

SELECT p.id, p.title, c.id, c.messageFROM post pLEFT join comment cON c.post_id = p.id AND c.id =                  (SELECT MAX(c.id) FROM comment c2 WHERE c2.post_id = p.id)

You'll want to join to a sub-query that returns the last comment for the post. For example:

select post.id, post.title. lastpostid, lastcommentmessagefrom postinner join(    select post.id as lastpostid, max(comment.id) as lastcommentmessage    from post    inner join comment on commment.post_id = post.id    group by post.id) lastcomment    on lastpostid = post.id