Geek Answers Handbook

How to get the last 5 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 the key here. Each user can have several comments.

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

The output should look like the original table, just limit user comments to the 5 most recent for each user.

+6
sql sql-server
source share
4 answers

Assuming at least SQL Server 2005, so you can use the window function ( row_number ) and 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
+10
source share

Joe's answer is the best way to do this in SQL Server (at least I assume I'm not familiar with CTE). But here is 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
+2
source share
 SELECT TOP 5 * FROM table WHERE user_id = x ORDER BY comment_id ASC

I think he should do it.

0
source share

In SqlServer 2005, LIMIT is invalid.

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 you need to renumber them, for example). You might want to consider an alternative field, but the basic structure will be the same.

Please note that if you ordered by date, you would like to sort in descending order, and not in ascending order, for example.

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

More articles:


All Articles