|
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.
|
