The last N rows in each group

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.

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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.

This was first published in November 2004

Back to top