How can I select the latest record having one state where no later records exist with any other state?

Question

I want to select the last record (by datetime) having a given state where no later records exist with any other state for each given "widget".

widget     state    timestamp
------     -----    ---------
foo        up       2011-02-11 12:34:56
foo        down     2011-02-10 10:01:10
bar        down     2011-02-09 09:00:20
bar        up       2011-02-08 08:11:20
baz        down     2011-02-07 07:04:18
baz        up       2011-02-06 06:05:18

and many more such records - more than two per widget and more than the two states shown. The timestamps are not ordered (they records are inserted without regard to the timestamp). The unique key is an ID that is generated externally (by another application).

For example, I want to select records with state = "down" and the result should include the records for "bar" and "baz", but not for "foo" since there is a later state for "foo" that is not "down".

I'm using a natural self join to select the latest event for a widget for a different query and think that maybe building on that in some way would work for this one.

Answer 1

SELECT widget, MAX(`timestamp`) AS ts
FROM tableX AS t
WHERE state = 'down'
GROUP BY widget
HAVING NOT EXISTS
       ( SELECT *
         FROM tableX AS tt
         WHERE tt.widget = t.widget
           AND tt.state <> 'down'
           AND tt.`timestamp` > MAX(t.`timestamp`)
       ) ;

I think that you'll need two indices, one on (widget, state, timestamp) and one on (widget, timestamp, state) for efficiency.

---

This will work, too, and will be needing only one index, on (widget, timestamp, state):

SELECT t.widget, t.`timestamp`
FROM 
        tableX AS t
    JOIN
        ( SELECT widget, MAX(`timestamp`) AS ts
          FROM tableX
          GROUP BY widget
        ) AS tm
            ON  tm.widget = t.widget
            AND tm.ts = t.`timestamp`
WHERE t.state = 'down' ;

Tested both at SQL-Fiddle: test