SQL Query to get the latest user comments on a blog post limited to one for each user?

Question

I have several users posting comments on several blog posts. Users can comment several times in each blog post. I need SQL Query (sql server 2008) to get the latest comment for each user set in BlogPostId.

Let's say that 3 users posted a total of 10 comments on a specific blog post. For Blog Post # 1, User A posted 5 comments, User B posted 2 comments, and User C posted 3 comments.

For a specific BlogPostId (e.g. # 1), how can I get the last comment for each user restricting it to only their last comment (e.g., one comment for the user)?

The final result should contain three lines (e.g..)

(User A) CommentId, BlogPostId, UserId, CommentData
(User B) CommentId, BlogPostId, UserId, CommentData
(User C) CommentId, BlogPostId, UserId, CommentData

Answer 1

There are many ways to do this. Using the ranking function:

with cte as (
   select *, row_number() over (partition by UserId order by PostedtDate desc) as rn
   from Comments
   where PostId = @postId)
select *
from cte
where rn = 1;

using aggregates and cross-applications:

with cte as (
   select distinct UserId
   from Comments
   where PostId = @postId)
select * 
from cte
cross apply (
   select top(1) *
   from Comments c
   where c.UserId = cte.UserId
   and c.PostId = @postId
order by PostedDate desc);

In the end, the real important question is not how you request this information (which is trivial, and you will probably get 10 answers in 10 minutes), but how you plan your scheme to make this request quickly.

Answer 2

Since this is MS SQL 2008, why not use ROW_NUMBER ()

WITH last_comment AS 
(
    SELECT  ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY DateAdded) as RowIndex,
            *
    FROM    BlogPost B 
    WHERE   BlogPostId = @BlogPostId
)
SELECT *
FROM    last_comment
WHERE   RowIndex = 1

Answer 3

, , :

SELECT
    C1.comment_id,
    P.blog_post_id,
    C1.user_id,
    C1.comment_data
FROM
    Blog_Posts P
LEFT OUTER JOIN Comments C1 ON
    C1.blog_post_id = P.blog_post_id
LEFT OUTER JOIN Comments C2 ON
    C2.blog_post_id = C1.blog_post_id AND
    C2.user_id = C1.user_id AND
    C2.comment_date > C1.comment_date
WHERE
    P.blog_post_id = @blog_post_id AND
    C2.comment_id IS NULL

C2.comment_id NULL, , , C1 . , .

Answer 4

:

select * from comments where blogpost_id = 3333 and comment_id in
(select max(comment_id) from comments where blogpost_id = 3333 group by UserId)

Answer 5

select Top (1) CommentID, Comment, UserName, CreateDate from the comments, where BlogPostID = 1 Order by CreateDate, UserName, Comment, CommentID group by UserName

OK, it's just right from my head, not knowing your database, you need to get all the comments for your blog, then you need to sort by the date the comment was created, either asc or desc, then you need to group it for the user and select the first from the list ... in depending on how you sort, you can also choose the latter, too ...

NTN