Query for returning "nearby" rows?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

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


Re: Query for returning "nearby" rows?

Kevin wrote:
Quoted text here. Click to load it

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';

Bill K.

Re: Query for returning "nearby" rows?

Hi Bill,

Thanks, that's exactly what I needed!

- Kevin
Bill Karwin wrote:
Quoted text here. Click to load it

Site Timeline