views:

38

answers:

5

I have multiple users submitting comments on multiple blog posts. Users can comment multiple times on each blog post. I need a SQL Query (sql server 2008) to get the last comment for each User given a BlogPostId.

Lets say 3 users submit a total of 10 comments on a specific blog post. For Blog Post #1, User A has submitted 5 comments, user B has submitted 2 comments, and user C has submitted 3 comments.

For a specific BlogPostId (eg. #1) how would I get the latest comment for each user limiting it to their most recent comment only (eg. one comment per user)?

The end result should produce three rows (eg.)

(User A) CommentId, BlogPostId, UserId, CommentData
(User B) CommentId, BlogPostId, UserId, CommentData
(User C) CommentId, BlogPostId, UserId, CommentData
+1  A: 

One of several possible solutions, with a bit of guessing towards your schema since you didn't post that information in your question:

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

If C2.comment_id is null then it must be because no later comment could be joined in, so C1 must be the latest. If there is an exact tie in the time you might get two comments back for the same user.

Tom H.
A: 

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

OK this is just straight from my head without knowing your database You need to get all the comments for your blogpost then you need to sort by the date the comment is created either asc or desc then you need to group it for the user and pick the first from the list... depending how you sort you can also choose the last, too...

HTH

server info
A: 

You can try this:

select * from comments where blogpost_id = 3333 and comment_id in
(select max(comment_id) from comments where blogpost_id = 3333 group by UserId)
Pablo Santa Cruz
+2  A: 

Since it's 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
Ender
This one works too thanks!
Marc M
+1  A: 

There are many many ways to do it. Using a 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 apply:

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 query this information (which is trivial and you'll probably get 10 answers in 10 minutes), but how do you design your schema to make this query fast.

Remus Rusanu