Skip to content
Advertisement

SQL delete duplicate rows based on multiple fields

I have the following table in sql:

What I need to do is identify where a trip_id as multiple instances of a certain stop_id (in this case stop_id 2).

I then need to delete any duplications leaving only the one with the latest departure time.

So given the above table Id delete the row with id 2 and be left with:

I have managed to do this with a series of sql queries but I hit the N+1 issue and it takes ages.

Can anyone recommend a way I may be able to do this in one query? Or at the very least identify all the ids of rows that need deleting in one query?

Im doing this in ruby on rails so if you prefer an active record solution I wouldn’t hate you for it 🙂

Thanks in advance.

Advertisement

Answer

You may try the following logic:

In plain English, this says to scan your entire table, and delete any record for which we can find another record with an identical trip_id and stop_id, where the departure time is also greater than that of the record being considered for deletion. If we find such a match, then it is a duplicate according to your definition.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement