MYSQL delete duplicate record in same group and keep only one [duplicate]

Question

I searched but all of the questions come up with delete duplicate record and keep only one latest in table, but my is a bit difference, I want to delete duplicate record in the same group but keep only latest record of that group. Here is an example:

ID  Device_ID    Time                   Content Text
1   abc          2013-11-30 15-30-01    I love you
2   abc          2013-11-30 15-30-01    I love you
3   def          2013-10-30 12-12-02    I love you
4   def          2013-10-30 12-12-02    I love you
5   ghj          2013-09-30 11-12-02    I love you
6   ghj          2013-09-30 11-12-02    I love you

So there are some devices having same content with other device, I would like find a way to delete duplicate content in each device id and delete duplicate and keep the latest.

If I select group by content text then delete it from there then it would delete all others content from other device as well and I can't do that.

Assume I have 50 million records. So is there a way I can delete duplicate record on each device id the result would be like only having I love you content text on each device id.

I can't make my column content text as unique during inert because there might be some device id using same content text. Or maybe unique time but then each device id might insert same time but difference content text...

I would like res

ID  Device_ID    TimeContent            Text

2   abc          2013-11-30 15-30-01    I love you

4   def          2013-10-30 12-12-02    I love you

6   ghj          2013-09-30 11-12-02    I love you

Answer 1

Try:

DELETE FROM tblA 
WHERE id NOT IN (
          SELECT DISTINCT id FROM (
          SELECT  MAX(id) as id FROM tblA 
          GROUP BY Device_ID, Time, ContentText 
          HAVING COUNT(*) > 1 ) A          
    );

See Demo