CODEWITHSUNDEEP
sql-servergreatest-n-per-group
TPR
TPR

Reputation: 2577

Implementing MOST recent comment - SQL Server

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.

Upvotes: 1

Views: 222

Answers (2)

RBarryYoung
RBarryYoung

Reputation: 56785

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

Upvotes: 1

James McNellis
James McNellis

Reputation: 355387

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.

Upvotes: 3

Related Questions