TSQL left join and only last row from right

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 th开发者_如何学JAVAis query?

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.

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)

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, lastcommentmessage
from post
inner 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

Couple of options....

One way is to do the JOIN on:

SELECT TOP 1 comment.message FROM comment ORDER BY comment.id DESC

(note I'm assuming that comment.id is an Identity field)

what version of SQL Server? If you have the Row_Number() function available you can sort your comments by whatever "first" means to you and then just add a "where RN=1" clause. Don't have a handy example or the right syntax off the top of my head but do have tons of queries that do exactly this. Other posts are all in the 1,000's of ways you could do this.

I'd say profile it and see which one performs best for you.

You didn't say the specific name of your date field, so I filled in with [DateCreated]. This is essentially the same as AGoodDisplayName's post above, but using the date field instead of relying on the ID column ordering.

SELECT post.id, post.title, comment.id, comment.message
FROM post p
LEFT OUTER JOIN comment
ON comment.id = (
    SELECT TOP 1 id
    FROM comment
    WHERE p.id = post_id
    ORDER BY [DateCreated] ASC
)