Alex Rivera | Logout

TSQL left join and only last row from right

Asked 2010-02-17T14:44:49.017
33

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?

sql sql-server t-sql left-join
Edit
Report

1 Answer

1

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.

answered 2010-02-17T14:54:24.833

Your Answer