Mysql – How to select the latest record of each hour in a day

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

Your first HUGE mistake (as @ypercube pointed out) is not using a DATETIME type for a date-time type variable. TIMEs are not VARCHARs and doing this will mess up your queries, make your app non-portable and will confuse the optimiser.

The other HUGE problem is MySQL. It doesn't perform this sort of query properly. Your query is ambiguous - PostgreSQL for example will throw an error if you try and run such a query.

For example

mysql> SELECT id, MAX(posted_date) FROM example GROUP BY id;
+------+------------------+
| id   | MAX(posted_date) |
+------+------------------+
|    1 | 2015-03-27       |
|    2 | 2015-03-15       |
+------+------------------+
2 rows in set (0.00 sec)

mysql> 

is unambiguous and gives the correct answer.But, because you have tried to use a SELECT * FROM example, the MySQL engine doesn't know which values to return and (stupidly enough) appears (at least in the several examples of this problem that I have seen) to return the correct unambiguous values, but the other are random - so you could get id = 1 (correct) with cname = 'ghi' (incorrect).

Check out MySQL's SQL modes - there's one (ONLY_FULL_GROUP_BY) that disallows this behaviour. It should be switched on at ALL times.

To get your query to work properly, for starters, use a DATETIME type (makes the subquery below easier) and then do a subquery for cname where your DATETIME value matches that for the correct cname. HTH,

[EDIT]

Using this schema and data, your query becomes relatively easy.

CREATE TABLE example
(
id int(11),
cname varchar(10),
posted_datetime datetime
); 


INSERT INTO example
VALUES (1, 'abc', '2015-03-26 04:25');

INSERT INTO example
VALUES (1, 'def', '2015-03-27 16:30');

INSERT INTO example
VALUES (2, 'ghi', '2015-03-11 02:25');

INSERT INTO example
VALUES (2, 'jkl', '2015-03-15 12:25');

And your working query is:

SELECT e.id, e.cname, e.posted_datetime 
FROM example e
  JOIN
  (
    SELECT id, MAX(posted_datetime) AS posted_datetime
    FROM example 
    GROUP BY id
  ) m
  ON  m.id = e.id 
  AND m.posted_datetime = e.posted_datetime ;

or check it out here: SQLfiddle