Correlated Subquery (on same table)

Question

I have a table that keeps up with device state changes in a room, and need to write a query that lists the amount of time since the last state change. The table consists of the fields, new_state, prev_state, room, timestamp. My latest try was

SELECT a.room, a.prev_state, a.new_state, 
timediff(a.timestamp, b.timestamp) from   status_change as a
(SELECT b.timestamp from status_change as b 
 WHERE a.timestamp<b.timestamp and a.room=b.room
 ORDER BY b.timestamp DESC LIMIT 1)

Hopefully that conveys what I'm trying to accomplish.

Thanks

Answer 1

Try this (and you should put a primary key on it, especially for performance reasons)

SELECT status_change.*
 , previous.timestamp AS earlier_timestampe
 , TIMEDIFF(status_change.timestamp, previous.timestamp) AS time_between
FROM status_change 
LEFT JOIN status_change AS previous
  ON status_change.room = previous.room
    AND status_change.timestamp > previous.timestamp
LEFT JOIN status_change AS inbetweener
  ON status_change.room = inbetweener.room
    AND inbetweener.timestamp BETWEEN previous.timestamp AND status_change.timestamp
WHERE inbetweener.room IS NULL
ORDER BY status_change.timestamp DESC;