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.