CODEWITHSUNDEEP
sqlsql-server
myforums
myforums

Reputation: 301

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 limit user's comments to 5 most recent for every user.

Upvotes: 6

Views: 935

Answers (4)

Larry Lustig
Larry Lustig

Reputation: 51000

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

Upvotes: 2

Nathan
Nathan

Reputation: 10784

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

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

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

Upvotes: 11

Jacob
Jacob

Reputation: 3801

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

I think that should do it.

Upvotes: -1

Related Questions