MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE IF NOT EXISTS `MyTable` (
  `Id` int(6) unsigned NOT NULL,
  `NumericValue` float NOT NULL,
  `UTCTimestamp` timestamp NOT NULL,
  PRIMARY KEY (`Id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `MyTable` (`Id`, `NumericValue`, `UTCTimestamp`) VALUES
  (1, 32.33, '2018-05-03 14:12:06'),
  (2, 27.45, '2018-05-03 11:43:12'),
  (3, 11.12, '2018-05-03 8:21:26'),
  (4, 6.56, '2018-05-03 3:58:11'),
  (5, 88.56, '2018-05-03 8:43:43'),
  (6, 34.65, '2018-05-03 8:34:43'),
  (7, 65.67, '2018-05-03 11:31:40');
WITH cte AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY HOUR(UTCTimestamp) ORDER BY UTCTimestamp) AS rn
        , ROW_NUMBER() OVER(PARTITION BY HOUR(UTCTimestamp) ORDER BY UTCTimestamp DESC) AS rn2
FROM MyTable
)
SELECT HOUR(UTCTimestamp), ID, NumericValue, UTCTimestamp
FROM cte
WHERE 1 IN (rn, rn2)
ORDER BY HOUR(UTCTimestamp) ASC


HOUR(UTCTimestamp)
ID
NumericValue
UTCTimestamp


3
4
6.56
2018-05-03 03:58:11

8
5
88.56
2018-05-03 08:43:43

8
3
11.12
2018-05-03 08:21:26

11
2
27.45
2018-05-03 11:43:12

11
7
65.67
2018-05-03 11:31:40

14
1
32.33
2018-05-03 14:12:06


WITH cte AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY HOUR(UTCTimestamp) ORDER BY UTCTimestamp) AS rn
        , ROW_NUMBER() OVER(PARTITION BY HOUR(UTCTimestamp) ORDER BY UTCTimestamp DESC) AS rn2
FROM MyTable
)
SELECT HOUR(c1.UTCTimestamp), c1.ID, c1.NumericValue, c1.UTCTimestamp, c2.ID, c2.NumericValue, c2.UTCTimestamp
FROM cte c1
JOIN cte c2
  ON HOUR(c1.UTCTimestamp) = HOUR(c2.UTCTimestamp)
  AND c1.rn=1
  AND c2.rn2=1
ORDER BY HOUR(c1.UTCTimestamp) ASC


HOUR(c1.UTCTimestamp)
ID
NumericValue
UTCTimestamp
ID
NumericValue
UTCTimestamp


3
4
6.56
2018-05-03 03:58:11
4
6.56
2018-05-03 03:58:11

8
3
11.12
2018-05-03 08:21:26
5
88.56
2018-05-03 08:43:43

11
7
65.67
2018-05-03 11:31:40
2
27.45
2018-05-03 11:43:12

14
1
32.33
2018-05-03 14:12:06
1
32.33
2018-05-03 14:12:06

HOUR(UTCTimestamp)	ID	NumericValue	UTCTimestamp
3	4	6.56	2018-05-03 03:58:11
8	5	88.56	2018-05-03 08:43:43
8	3	11.12	2018-05-03 08:21:26
11	2	27.45	2018-05-03 11:43:12
11	7	65.67	2018-05-03 11:31:40
14	1	32.33	2018-05-03 14:12:06