Slightly complex mySQL Query

Question

Hello,

I have a table which has the following

id     timestamp
-------------------------------------
 1     1247046037
 4     1247047437
 5     1247047438
 6     1247048738

Now I want to return all those ids which are 3 minutes apart from the previous id. In the above example it should return 1,4,6.

How should I go about doing this? Kinda confused. I want to avoid joins and everything else as much as possible.

UPDATE Sorry, I forgot to mention that the IDs may not be sequential so cannot use id = id+1

Answer 1

You are not going to be able to do this without joins, as far as I can tell. This should do what you need

select t1.id
from mytable as t1
    left join mytable as t2 on t1.id = (t2.id + 1)
where t2.id is null or (t1.timestamp - 180) > t2.timestamp

If the id field may have gaps, you'll need to introduce a row number field to do this. Probably more efficient to add an autonumber field to your table if you'll be running this regularly.

select t1.id
from 
    (select mytable.*, @rownum:=@rownum+1 as rownum from mytable, (select @rownum:=0) r order by id) as t1
    left join 
    (select mytable.*, @rownum2:=@rownum2+1 as rownum from mytable, (select @rownum2:=0) r order by id) as t2 on t1.rownum = (t2.rownum + 1)
where t2.id is null or (t1.timestamp - 180) > t2.timestamp

Answer 2

You can use a null-self-join to get two IDs next to each other:

SELECT thing2.id
FROM things AS thing1
JOIN things AS thing2 ON thing1.id<thing2.id
LEFT JOIN things AS nothing ON nothing.id BETWEEN thing1.id+1 AND thing2.id-1
WHERE nothing.id IS NULL
AND thing2.timestamp=thing1.timestamp+180

ie. there is no row with an id between thing1's and thing2's.

(This gets rows which are exactly 3 minutes apart; it's not clear from the question whether you really want rows that are 3-or-more minutes or 3-or-fewer minutes apart.)

Answer 3

This would work perfectly in MSSQL. Would be interesting to see if MySQL can handle this tie-breaker query:

SELECT      curr.*
            --//,prev.*
            --//,curr."timestamp" - prev."timestamp" 
FROM        @Table curr
LEFT JOIN   @Table prev
        ON  prev.ID = ( SELECT TOP 1 prev_match.ID 
                        FROM        @Table prev_match 
                        WHERE       prev_match.ID < curr.ID 
                        ORDER BY    prev_match.ID DESC)
WHERE       curr."timestamp" - prev."timestamp" >= 180
        OR  prev.ID IS NULL --// cover special case when there is no previous at all

But if you leave without having the row with id=0, I would remove the OR from WHERE condition and change LEFT JOIN into INNER JOIN

Answer 4

If you want to avoid a join, I think you could use subqueries instead, but I would expect it to be slower so would recommend against it:

SELECT t.id
FROM yourTable t
WHERE t.timestamp -
 (SELECT t3.timestamp --This gives the time of the id before
  FROM yourTable t3 
  WHERE id = 
   (SELECT ISNULL(MAX(t2.id),-1)  --This gives the id before or -1 if first id.
    FROM yourTable t2
    WHERE t2.id < t.id)
 )
> 180