List:General Discussion« Previous MessageNext Message »
From:Rolando Edwards Date:October 17 2006 7:16pm
Subject:Re: sql query
View as plain text  
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>
Cc: mysql@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

Dan

On 10/17/06, Peter <viva_peter@stripped> 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/mysql?unsub=1
>
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1


Thread
sql queryPeter17 Oct
  • Re: sql queryDan Buettner17 Oct
    • Re: sql queryRolando Edwards17 Oct
      • Re: sql queryPeter17 Oct
  • Re: sql queryPeter Brawley17 Oct