MySQL - Remove duplicates based on same date entry

Question

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 1

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

Answer 2

you simple use group by function to get your result by

SELECT * FROM table_name GROUP BY date;

but Some time group by not work in this situation properly see here

you should use

1)using min(id)

SELECT * FROM your_table GROUP BY date HAVING MIN(id);

2)using min(date)

SELECT * FROM your_table GROUP BY date HAVING MIN(date);