Query for returning "nearby" rows?

Let's say I have a query that will return a collection of rows with a
datetime field:

SELECT User,Activity,Timestamp FROM `table` WHERE Activity = 'TEST'

Which returns
'Steve','TEST','2006-01-01 12:00:00'
.... etc...

So the query tells us who and when did activity "TEST."

Is there another query I could write that would tell me who performed a
test and what activities they performed within 10 minutes of test?

I'm figuring it's possible with a self-join or subquery, but I can't
figure it out.

Thanks!

Re: Query for returning "nearby" rows?

Kevin wrote:
> Is there another query I could write that would tell me who performed a
> test and what activities they performed within 10 minutes of test?

Here a possible solutions:

SELECT t1.User, t1.Activity, t1.Timestamp
FROM `table` AS t1 JOIN `table` AS t2
ON t1.User = t2.User AND
t1.Timestamp BETWEEN
(t2.Timestamp - INTERVAL 10 MINUTE) AND
(t2.Timestamp + INTERVAL 10 MINUTE)
WHERE t2.Activity = 'TEST';


Regards,
Bill K.

Re: Query for returning "nearby" rows?

Hi Bill,

Thanks, that's exactly what I needed!

- Kevin
Bill Karwin wrote:
> Kevin wrote:
>> Is there another query I could write that would tell me who performed
>> a test and what activities they performed within 10 minutes of test?
>
> Here a possible solutions:
>
> SELECT t1.User, t1.Activity, t1.Timestamp
> FROM `table` AS t1 JOIN `table` AS t2
> ON t1.User = t2.User AND
> t1.Timestamp BETWEEN
> (t2.Timestamp - INTERVAL 10 MINUTE) AND
> (t2.Timestamp + INTERVAL 10 MINUTE)
> WHERE t2.Activity = 'TEST';
>
>
> Regards,
> Bill K.