Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

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

share|improve this answer
    
I had to use @ts = MAX(`timestamp`) AS ts and AND tt.`timestamp` > @ts in order to avoid an error message "ERROR 1247 (42S22): Reference 'ts' not supported (reference to group function)". I'm still getting some false returns where later non-down records exist. Do you have any suggestions for diagnosing this? So far I've tried splitting the queries and the subquery compared to a literal (from MAX(`timestamp`) substituted for ts returns a record which should cause the HAVING NOT EXISTS to fail but doesn't. –  Dennis Williamson Apr 18 '12 at 16:06
    
@Dennis: Sorry, I missed your comment. Try the update. Tried at SQL-Fiddle test –  ypercube Jul 15 '12 at 9:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.