title: Correlated Subquery (on same table)
body: <p>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</p>
  
  <pre><code>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&lt;b.timestamp and a.room=b.room
   ORDER BY b.timestamp DESC LIMIT 1)
  </code></pre>
  
  <p>Hopefully that conveys what I'm trying to accomplish.</p>
  
  <p>Thanks</p>