MySQL – Join Two Tables and Limit by First Input

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

Here's one way to do what you describe:

SELECT <columns>
FROM (
    SELECT <columns> 
    FROM foo
    WHERE <condition>     
    ORDER BY <expression-list-1> 
    LIMIT 15
) AS T 
LEFT JOIN bar 
    ON T.a = bar.a 
ORDER BY <expression-list-2> ;

You basically get 15 rows from foo according to some criteria and join the result with bar.

The first, internal ORDER BY list affects which 15 rows will be selected. The second, final list affects only the presentation ordering of the results.

The two ORDER BY lists do not need to be identical, although in practice they'll often be the same or the second an extension of the first, to resolve ties (how the children of the same parent are ordered).