How can I find which two rows have timestamps closest to each other?

Question

I'm building a web application for location-based check ins, sort of like a local 4square, but based on RFID tags.

Anyway, each check-in is stored in a MySQL table with a userID and the time of the check-in as a DATETIME column.

Now I'd like to show which users have the closest check-in times between different stations.

Explanation: Let's say user A checked in at 21:43:12 and then again at 21:43:19. He moved between stations in 7 seconds.

There are thousands of check-ins in the database, how do I write SQL to select the users with the two closest check-in times?

Answer 1

Try this:

select
    a.id,
    b.id,
    abs(a.rfid-b.rfid)
from
    table1 a,
    table1 a
where
    a.userID=b.userID
    // and any other conditions to make it a single user
group by
    a.id,
    b.id,
    a.rfid,
    b.rfid
order by
    abs(a.rfid-b.rfid) desc
limit 1