How to delete duplicate data from MySQL except latest data

Question

I want to delete records from mysql table

I have table like this

I am checking here if (date, url, price, hotelName) is same then remove except one

id | hotelName | price | url      | date        |
-------------------------------------------------
1  | abcd      | 20$   | abcd.com | 21 jan 2019 |
2  | abcd      | 24$   | abcd.com | 22 jan 2019 |
3  | wzyz      | 10$   | wzyz.com | 21 jan 2019 |
4  | abcd      | 20$   | abcd.com | 21 jan 2019 |
5  | wzyz      | 15$   | wzyz.com | 22 jan 2019 |
6  | wzyz      | 15$   | wzyz.com | 22 jan 2019 |

In this table you can see duplicate records is id [1,4] and [5,6]

I want to delete duplicate records from this table except latest data

After deleting this table should look like

id | hotelName | price | url      | date        |
-------------------------------------------------
2  | abcd      | 24$   | abcd.com | 22 jan 2019 |
3  | wzyz      | 10$   | wzyz.com | 21 jan 2019 |
4  | abcd      | 20$   | abcd.com | 21 jan 2019 |
6  | wzyz      | 15$   | wzyz.com | 22 jan 2019 |

Answer 1

If your table is not too big, this is a short and straight-forward syntax :

DELETE t1 
FROM 
    mytable t1 
    CROSS JOIN t2 
WHERE 
    t1.id < t2.id 
    AND t1.hotelName = t2.hotelName 
    AND t1.date      = t2.date
    AND t1.url       = t2.url
    AND t1.price     = t2.price

Anoter solution, less resource-consuming :

DELETE FROM mytable
WHERE id NOT IN (
    SELECT MAX(t.id) FROM mytable t GROUP BY t.hotelName, t.date, t.url, t.price
)