Get records with highest value grouped by datetime hourly

Question

I have I think a variation of the common case described in

Get records with highest/smallest <whatever> per group

This is the table with data

id  | value1 | value2 | curdate    | curtime
 1  |    234 |    500 | 2014-10-27 | 12:00:00
 2  |    200 |    400 | 2014-10-27 | 12:05:00
 3  |    300 |    600 | 2014-10-27 | 12:10:00
 4  |    400 |    800 | 2014-10-27 | 12:15:00
 5  |    150 |    700 | 2014-10-27 | 12:20:00
 ...
10  |    134 |    530 | 2014-10-27 | 13:00:00
12  |    220 |    420 | 2014-10-27 | 13:05:00
13  |    330 |    630 | 2014-10-27 | 13:10:00
14  |    440 |    840 | 2014-10-27 | 13:15:00
15  |    120 |    750 | 2014-10-27 | 13:20:00

I want a report of the max value2 per hour and the corresponding value1:

value1 | value2 | curdate    | curtime
   400 |    800 | 2014-10-27 | 12:00:00
   440 |    840 | 2014-10-27 | 13:00:00

I was trying to apply the "standard" solution specified in the answer above but with no success. Using MySQL.

SELECT t1.*
FROM Table AS t1
LEFT OUTER JOIN `Table` AS t2
ON t1.GroupId = t2.GroupId AND t1.OrderField < t2.OrderField
WHERE t2.GroupId IS NULL

I tried something like:

SELECT t1.*
FROM Table AS t1
LEFT OUTER JOIN Table AS t2
ON SUBSTR(CONCAT(t1.curdate, ' ', t1.curtime), 1, 13) = SUBSTR(CONCAT(t2.curdate, ' ', t2.curtime), 1, 13) AND t1.value2 < t2.value2
WHERE ???? IS NULL   <--- not sure what to put here

Please help. Thanks.

Answer 1

Sample data:

CREATE TABLE t
    (`id` int, `value1` int, `value2` int, `curdate` date, `curtime` time)
;

INSERT INTO t
    (`id`, `value1`, `value2`, `curdate`, `curtime`)
VALUES
    (1, 234, 500, '2014-10-27', '12:00:00'),
    (2, 200, 400, '2014-10-27', '12:05:00'),
    (3, 300, 600, '2014-10-27', '12:10:00'),
    (4, 400, 800, '2014-10-27', '12:15:00'),
    (5, 150, 700, '2014-10-27', '12:20:00'),
    (10, 134, 530, '2014-10-27', '13:00:00'),
    (12, 220, 420, '2014-10-27', '13:05:00'),
    (13, 330, 630, '2014-10-27', '13:10:00'),
    (14, 440, 840, '2014-10-27', '13:15:00'),
    (15, 120, 750, '2014-10-27', '13:20:00')
;

Query:

SELECT t.*
FROM t
INNER JOIN (
    SELECT
    DATE_FORMAT(CONCAT(curdate, ' ', curtime), '%Y-%m-%d %h') AS datehour,
    MAX(value2) as maxv2
    FROM
    t
    GROUP BY datehour
) sq ON t.value2 = sq.maxv2 AND DATE_FORMAT(CONCAT(t.curdate, ' ', t.curtime), '%Y-%m-%d %h') = sq.datehour;

Result:

+------+--------+--------+------------+----------+
| id   | value1 | value2 | curdate    | curtime  |
+------+--------+--------+------------+----------+
|    4 |    400 |    800 | 2014-10-27 | 12:15:00 |
|   14 |    440 |    840 | 2014-10-27 | 13:15:00 |
+------+--------+--------+------------+----------+