Select youngest record

Question

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))

Answer 1

You want "current" data and you want "historical" data. So have two tables. Each time you get new data in, INSERT INTO Historical ... and INSERT INTOCurrent.. ON DUPLICATE KEY ..

Although this may not be as elegant (due to having redundant data), it makes the SELECTs a lot simpler.

Answer 2

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` >= (SELECT MIN(`date`)
                          FROM cte1))
SELECT id,room,`date`,`temp`
FROM cte2
WHERE rn=1

Answer 3

If you want to go KISS, You can ask for the table in order of the most recent date and limit to 1 row

SELECT * 
FROM mdata  
ORDER BY date DESC  
LIMIT 1