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 <[EMAIL PROTECTED]> To: Peter <[EMAIL PROTECTED]> Cc: mysql@lists.mysql.com 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 Dan On 10/17/06, Peter <[EMAIL PROTECTED]> wrote: > Hello, > > 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. > > Peter > 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/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
