SQL delete duplicate rows based on multiple fields

I have the following table in sql:

id | trip_id | stop_id | departure_time
----------------------------------------
1  |        1|        1|        06:25:00
2  |        1|        2|        06:35:00
3  |        1|        3|        06:45:00
4  |        1|        2|        06:55:00

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:

id | trip_id | stop_id | departure_time
----------------------------------------
1  |        1|        1|        06:25:00
3  |        1|        3|        06:45:00
4  |        1|        2|        06:55:00

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.

Answer

You may try the following logic:

DELETE
FROM yourTable t1
WHERE EXISTS (SELECT 1 FROM yourTable t2
              WHERE t2.trip_id = t1.trip_id AND
                    t2.stop_id = t1.stop_id AND
                    t2.departure_time > t1.departure_time);

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.