How can I select the latest record having one state where no later records exist with any other state?
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 &lt;&gt; 'down'
           AND tt.`timestamp` &gt; 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