Hurricane Hurricane - 4 years ago 177
MySQL Question

MySQL - Remove duplicates based on same date entry

I have this:


+----+---------------------+
| id | date |
+----+---------------------+
| 1 | 2017-01-01 12:30:00 |
| 2 | 2017-01-01 12:30:00 |
| 3 | 2017-01-02 00:00:00 |
| 4 | 2017-01-03 00:00:00 |
+----+---------------------+


This is part of a large dataset (with various joins).

I want to remove the entries with a duplicate date while keeping one of them i.e.:


+----+---------------------+
| id | date |
+----+---------------------+
| 1 | 2017-01-01 12:30:00 |
| 3 | 2017-01-02 00:00:00 |
| 4 | 2017-01-03 00:00:00 |
+----+---------------------+


Does anyone know a good approach for this. I had thought sorting then iterating through each row and deleting based on whether date has been encountered before. Or using COUNT/HAVING on the date.

Thanks.

Answer Source

You could use aggregation:

select min(id) as id, date
from t
group by date;

If you have additional columns, this won't work so well. Instead, use a filter on the where clause:

select t.*
from t
where t.id = (select min(t2.id) from t t2 where t2.date = t.date);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download