Implementing MOST recent comment - SQL Server

Question

So suppose I have a bunch of blog entries and I want to find out the most recent comment in each of them, how would I go about finding that out in SQL Server.

I have a list of integer id's of these blog entries in a temp table. Something like select top 1 does not work in this case.

The approach coming to my mind is looping, and we all know how much people prefer to avoid loops in SQL Server.

Answer 1

You can use a subquery in the SELECT statement. Something like:

SELECT  post.id, 
        most_recent_comment_id = 
            (SELECT TOP 1 comment.id 
             FROM comment 
             WHERE comment.post_id = post.id
             ORDER BY comment.date DESC)
FROM posts
ORDER BY posts.date

or something similar to that.

Answer 2

Well, here is one way:

SELECT c.*
FROM BlogComments c
JOIN #TempEntries t ON c.EntryID = t.EntryID
JOIN (
    SELECT m.EntryID, MAX(m.CommentID) AS CommentID
    FROM BlogComments m
    GROUP BY m.EntryID
    ) m  
        ON  m.EntryID = c.EntryID
        AND m.CommentID = c.CommentID