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

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 limi开发者_开发技巧t user's comments to 5 most recent for every user.

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

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

In SqlServer 2005, LIMIT is not valid.

Instead, do something like:

SELECT TOP(5) * FROM Comment WHERE user_id = x ORDER BY comment_id ASC

Note that this assumes that comment_id is monotonically increasing, which may not always be a valid assumption for identity fields (if they need to be renumbered for example). You may want to consider an alternate field, but the basic structure would be the same.

Note that if you were ordering by a date field, you would want to sort in descending order rather than ascending order, e.g.

SELECT TOP(5) * FROM Comment WHERE user_id = x ORDER BY last_updated DESC

SELECT TOP 5 * FROM table WHERE user_id = x ORDER BY comment_id ASC

I think that should do it.