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.
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
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