Delete duplicate order ids by keeping the tracking status - mysql

Question

I have a table called tracking_history. In this table parcel tracking history will be stored. For some reasons when ever the checkTrackStatus function executed all the existing tracking statuses are inserting repeatedly in the table. Here are the tracking statuses sequence. 'ACCEPTED','AT_SENDING_DEPOT','ON_THE_ROAD','AT_DELIVERY_DEPOT','DELIVERED' I am saving the tracking id , order id also in that table. So I need to have for every order id the latest tracking status should be there ('ACCEPTED','AT_SENDING_DEPOT','ON_THE_ROAD','AT_DELIVERY_DEPOT','DELIVERED') and remaining duplicate values should be removed. I tried with the below query.

`DELETE t1 FROM tracking_history t1, tracking_history t2 
WHERE t1.id < t2.id AND t1.order_id = t2.order_id` 

But this query is keeping only latest records and removing remaining all other. Means I am having all orders ids with DELIVERED Status only. How can I achieve to remove duplicate statuses by keeping the latest statuses ? Any help would be greatly appreciated.

Answer 1

I would want to keep the first id when a row is inserted rather than the last id. This is because of other information that might be useful -- notably the insertion time and who did the insertion. For that purpose, I would keep one row per status but phrase the logic as:

delete th
    from tracking_history th join
         (select order_status, status, min(id) as min_id
          from tracking_history th2
          group by order_status, status
         ) th2
         using (order_status, status)
    where id > min_id;

That said, this still doesn't seem right. After all, perhaps the status be the same on multiple rows. For instance, there could be multiple attempts to move a package from a depot to an address. What you really want is the most recent status per batch in tracking_history. I don't know if you have a "batch id" of some sort. But let me assuming that there is something, probably an entry date, that ties all the common values together.

In that case, you would want the latest status for each "batch":

delete th
    from tracking_history th join
         (select order_status, entry_date, max(id) as max_id
          from tracking_history th2
          group by order_status, entry_date
         ) th2
         using (order_status, entry_date)
    where id < min_id;