How do I get 5 latest comments (SQL query for SQL Server ) for each user?

Question

I have a table that looks like this: comment_id, user_id, comment, last_updated.

Comment_id is a key here. Each user may have multiple comments.

How do I get 5 latest comments (SQL query for SQL Server ) for each user?

Output should be similar to the original table, just limit user's comments to 5 most recent for every user.

Answer 1

Assuming at least SQL Server 2005 so you can use the window function (row_number) and the CTE:

;with cteRowNumber as (
    select comment_id, user_id, comment, last_updated, ROW_NUMBER() over (partition by user_id order by last_updated desc) as RowNum
        from comments
)
select comment_id, user_id, comment, last_updated
    from cteRowNumber
    where RowNum <= 5
    order by user_id, last_updated desc

Answer 2

Joe's answer is the best way to do this in SQL Server (at least, I assume it is, I'm not familiar with CTEs). But here's a solution (not very fast!) using standard SQL:

 SELECT * FROM comments c1
   WHERE (SELECT COUNT(*) FROM comments c2 
          WHERE c2.user_id = c1.user_id AND c2.last_updated >= c1.updated) <= 5