Dan's is correct because
the clause 'AND t1.id != t2.id'
prevents checking a row against itself
since the time diff with a row against
itself is zero, which is less than 300
----- Original Message -----
From: Dan Buettner <drbuettner@stripped>
To: Peter <viva_peter@stripped>
Sent: Tuesday, October 17, 2006 2:55:37 PM GMT-0500 US/Eastern
Subject: Re: sql query
Hi Peter -
Something like this ought to work:
SELECT t1.id_2 FROM mytable t1, mytable t2
WHERE t1.id_1 = t2.id_1
AND t1.id != t2.id
AND ABS( UNIX_TIMESTAMP(t1.date_time) - UNIX_TIMESTAMP(t2.date_time) ) <= 300
On 10/17/06, Peter <viva_peter@stripped> wrote:
> Lets suppose I have a table like this one
> id id_1 id_2 date_time
> 1 101 1000 2006-07-04 11:25:43
> 2 102 1001 2006-07-04 11:26:43
> 3 101 1005 2006-07-04 11:27:43
> 4 103 1000 2006-07-04 11:25:43
> I want to find all id_2 that has same id_1 and time difference in
> records is no more than 5 minutes ...
> I hope I explain well
> In this case this is record 1 and record 3.
> How can I do this ?
> Thanks in advance for your help.
> Send instant messages to your online friends http://uk.messenger.yahoo.com
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=1