Problem solve Get help with specific problems with your technologies, process and projects.

The last N rows in each group

I have a problem in selecting the last 8 records for each group in the table. There can be more than one record for a specific UserID. For example, for one UserID there are 20 records and for the other UserID there are only 2 records. The query should show the last 8 (starting from record no. 13 till 20) for the first UserID and the total 2 records for the other UserID.

This question is a variation of the "top N for each group" problem which has previously been covered a couple of times:

What both of those answers mention is the need to be prepared for situations where there are ties. However, in your case, this issue may not matter.

The key to your question is how you determine "lastness" in your rows.

One method is to have a timestamp. Assuming that your timestamp is accurate to some fraction of a second, it seems reasonable to assume that no two rows will have the same timestamp. Another method is to rely on the uniqueness of an auto-incrementing identifier. This strategy is quite safe, because the numbers will always increase, and, unless you take steps to circumvent this behaviour, the latest row will always have the highest identifier. Thus, expecting to receive rows 13 to 20 is actually okay (assuming there are no gaps in that sequence and that 20 is the highest value for that UserID -- but we're not going to retrieve them by number anyway).

Let's rewrite the self-join solution for your situation. Assume the incrementing identifier column is called ID.

 select t1.UserID , t1.id from yourtable as t1 inner join yourtable as t2 on t1.UserID = t2.UserID and t1.id <= t2.id group by t1.UserID , t1.id having count(*) <= 8 order by t1.UserID , t1.id desc

The self-join works like this: join every row (t1) to all the other rows with the same Userid (t2) which also have an ID that is equal to or greater. Remember, a greater ID is a later ID!

Now for the tricky part: count the number of rows that have a later ID than this row's ID, and if this number is less than or equal to 8, then this row must be within the latest 8. Simple, eh?

Yes, this solution will work even if a particular UserID has only 2 rows.

Dig Deeper on SQL Server Database Modeling and Design

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.