This question is a variation of the "top N for each group" problem which has previously been covered a couple of times:
Top ten sales for each salesman
This solution is for Microsoft Access, and uses the proprietary TOP syntax (which also works in Microsoft SQL Server).
Top two in each group in MySQL
This solution is actually not just for MySQL, because it uses generic self-join syntax.
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
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.