Select youngest record I have a MariaDB table "mdata" with the following schema:  

+-----+-----------+---------------------+------+
| id   | room      | date                | temp |
+------+-----------+---------------------+------+
| 1    | a2        | 2019-08-09 11:04:30 | 34.0 |
| 2    | a4_01     | 2019-08-09 11:04:30 | 32.0 |
| 3    | a3        | 2019-08-09 11:04:32 | 0.0  |
| 4    | a1_4      | 2019-08-09 11:04:32 | 27.0 |
| 5    | a2        | 2019-08-09 11:09:30 | 33.0 |
| 6    | a4_01     | 2019-08-09 11:09:30 | 34.0 |
| 7    | a3        | 2019-08-09 11:09:32 | 0.0  |
| 8    | a1_4      | 2019-08-09 11:09:32 | 29.0 |
+------+-----------+---------------------+------+


Every 5 minutes new temperature values will be written into the database.
I don´t want to overwrite the old values, because a timeline will be implemented later on.
Now I´ve created a web interface to display the youngest values.

Currently I´m struggling to find the right SELECT query.
It is important that the output of the query is in the same order as in the table, because it is put into an array and processed further.  



I tried something like this:  

SELECT * 
FROM mdata
WHERE temp IN
(SELECT temp FROM mdata WHERE date = (SELECT MAX(date) FROM mdata))
WITH cte AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY room ORDER BY `date` DESC) rn
             FROM mdata)
SELECT id,room,`date`,`temp`
FROM cte
WHERE rn=1


If a table contains huge array of data you can slightly restrict the amount of scanned records:

WITH 
cte1 AS (SELECT room, MAX(`date`) `date`
         FROM mdata
         GROUP BY room),
cte2 AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY room ORDER BY `date` DESC) rn
         FROM mdata
         WHERE `date` &gt;= (SELECT MIN(`date`)
                          FROM cte1))
SELECT id,room,`date`,`temp`
FROM cte2
WHERE rn=1