mysql - Removing duplicates by max date -

i have table in mysql database need remove duplicates from.

my table looks this:

unique_id    value    frequency    value_type    publication_date 1             6.5     1            2             2014-12-31 2             7.5     3            5             2014-06-04 3             6.5     1            2             2015-07-13 4             8.0     4            3             2010-12-31 

rows 1 , 3 duplicates except publication_date. need remove these duplicates keep row max publication date example want remove row 1 , keep row 3.

so far i've tried it's giving me many results on test table:

select t.* (select max(publication_date) most_recent_date  table_1  group `value`,frequency,value_type ) t1 join table_1 t  on t.publication_date = t1.most_recent_date; 

any appreciated.

thanks.

you can use join remove duplicates as

delete t1 table_1 t1 join table_1 t2 on t1.value = t2.value  , t1.frequency= t2.frequency  , t1.value_type = t2.value_type , t1.unique_id <> t2.unique_id , t1.publication_date < t2.publication_date ;