Hi All, I have this test table
+----------+------+------+------+--------+----------+ | username | key1 | key2 | key3 | method | timer | +----------+------+------+------+--------+----------+ | nhadie | aaa | bbb | ccc | INVITE | 00:00:10 | | nhadie | aaa | bbb | ccc | ACK | 00:00:14 | | nhadie | aaa | bbb | ccc | INVITE | 00:00:16 | | nhadie | aaa | bbb | ccc | ACK | 00:00:20 | | nhadie | aaa | bbb | ccc | INVITE | 00:00:22 | | nhadie | aaa | bbb | ccc | ACK | 00:00:26 | | nhadie | aaa | bbb | ccc | BYE | 00:00:42 | | nhadie | ddd | fff | eee | INVITE | 00:00:20 | | nhadie | ddd | fff | eee | ACK | 00:00:24 | | nhadie | ddd | fff | eee | INVITE | 00:00:26 | | nhadie | ddd | fff | eee | ACK | 00:00:30 | | nhadie | ddd | fff | eee | INVITE | 00:00:32 | | nhadie | ddd | fff | eee | ACK | 00:00:36 | | nhadie | ddd | fff | eee | BYE | 00:00:52 | +----------+------+------+------+--------+----------+ I tried this query: select t1.username as username, t1.method as t1method, t2.method as t2method, t1.timer as start, t2.timer as stop, unix_timestamp(t2.timer)-unix_timestamp(t1.timer) as timediff from test t1,test t2 where (t1.method = 'INVITE' and t2.method = 'BYE') and ((t1.key1 = t2.key1) and (t1.key2 = t2.key2) and (t1.key3 = t2.key3)); and this is the result: +----------+----------+----------+----------+----------+----------+ | username | t1method | t2method | start | stop | timediff | +----------+----------+----------+----------+----------+----------+ | nhadie | INVITE | BYE | 00:00:10 | 00:00:42 | 00:00:32 | | nhadie | INVITE | BYE | 00:00:16 | 00:00:42 | 00:00:26 | | nhadie | INVITE | BYE | 00:00:22 | 00:00:42 | 00:00:20 | | nhadie | INVITE | BYE | 00:00:20 | 00:00:52 | 00:00:32 | | nhadie | INVITE | BYE | 00:00:26 | 00:00:52 | 00:00:26 | | nhadie | INVITE | BYE | 00:00:32 | 00:00:52 | 00:00:20 | +----------+----------+----------+----------+----------+----------+ but what i want to actually achieve is to get only the time of the first INVITE and then it's corresponding BYE so it should look like this +----------+----------+----------+----------+----------+----------+ | username | t1method | t2method | start | stop | timediff | +----------+----------+----------+----------+----------+----------+ | nhadie | INVITE | BYE | 00:00:10 | 00:00:42 | 00:00:32 | | nhadie | INVITE | BYE | 00:00:20 | 00:00:52 | 00:00:32 | ------------------------------------------------------------------- Is that possible? What else can I do to have this output? Thank You in Advanced. Regards, Nhadie ________________________________________________ Message sent using UebiMiau 2.7 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
