TSQL left join and only last row from right?

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.

Thanks for answer. I use next code. SELECT post.Id, post.

Title,c. Id as comment_id,c. Message from post left outer join (select comment.

Id,comment. Post_id,comment. Message, ROW_NUMBER() OVER (PARTITION BY comment.

Post_id ORDER BY comment. Date DESC) AS rn from comment) c on post.Id=c. Post_id where c.

Rn=1 or c. Rn is null – barbarian Feb 17 '10 at 15:24.

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 ).

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).

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.

I use SQL SERVER 2005 – barbarian Feb 17 '10 at 15:21.

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

1 questioner has flagged it SQL server. – No Refunds No Returns Feb 17 '10 at 14:55.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.