How to keep the latest dataset if there are more than 1 datasets in one day

user_13794172 0
Viewed 19

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.

mysql
1 Answers

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