CODEWITHSUNDEEP
sqlpostgresqlduplicatessql-delete
Brad
Brad

Reputation: 8668

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.

Upvotes: 1

Views: 62

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

try like below

DELETE FROM table a
WHERE a.ctid <> (SELECT max(b.ctid)
                 FROM   table b
                 WHERE  a.stop_id = b.stop_id)

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try below way -

   DELETE FROM tablename
   WHERE id in
    (
    select id from
      (select *, row_number() over(partition by stop_id order by departure_time desc) as rn from tablename)aa
    )A where rn>1

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

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.

Upvotes: 3

Related Questions