i have a table which is updated random more than once a day. Every update contains about 2000 rows. I want to keep the latest dataset per day and remove the more old rows. I constructed an example:
The table "date" contains:
+-------+-------+---------+---------------------+
| title | hhm | hhm_sum | updated |
+-------+-------+---------+---------------------+
| 74142 | 21525 | 5874136 | 2020-06-15 00:00:00 |
| 74142 | 5263 | 2145 | 2020-06-22 00:00:00 |
| 74142 | 21254 | 21458 | 2020-06-22 04:00:00 |
| 74142 | 21458 | 3652 | 2020-06-22 08:00:00 |
| 74142 | 2158 | 1257 | 2020-06-20 00:00:00 |
+-------+-------+---------+---------------------+
With: SELECT * FROM test.date WHERE updated > DATE_SUB(DATE(NOW()), INTERVAL 24 HOUR);
i get the datasets for the last 24hours:
+-------+-------+---------+---------------------+
| title | hhm | hhm_sum | updated |
+-------+-------+---------+---------------------+
| 74142 | 5263 | 2145 | 2020-06-22 00:00:00 |
| 74142 | 21254 | 21458 | 2020-06-22 04:00:00 |
| 74142 | 21458 | 3652 | 2020-06-22 08:00:00 |
+-------+-------+---------+---------------------+
I want to remove the older rows on the same day and keep the newest. How can i get and handle this value?
Maybe anyone can help?
Thank you very much.
If you want to keep the single newest row, sort by updated desc and take the first row.
select *
from test_date
order by updated desc
limit 1
If there can be multiple rows which all have the newest updated, do a subquery to get the max(updated).
select *
from test_date
where updated = (
select max(updated) from test_date
)