Reputation: 2577
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
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
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