TSQL left join and only last row from right

Question

Hi,

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

Subquery:

SELECT post.id, post.title,comment.id,comment.message 
from post 
left outer join comment 
on post.id=comment.post_id 
where  comment.Id = (select max(c2.Id) from comment c2 where  post.id = c2.post_id  )

Answer 2

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

Answer 3

SELECT  post.id, post.title, comment.id, comment.message
FROM    post
OUTER APPLY
        (
        SELECT  TOP 1 *
        FROM    comment с
        WHERE   comment.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 4

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)

Answer 5

T-SQL is Sybase (as well as MS SQL Server), Sybase does not have 'top' keyword.

Answer 6

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.