Removing Duplicates From Multiple Unique Columns

Question

I am accessing table that takes in every encounter between two vehicles (i do not have permissions to change this table). When an encounter occurs, it'll take in one row for each perspective of the encounter- Vehicle X encountered Vehicle Y and another row for Vehicle Y encountered Vehicle X. Here's some sample data:

Location  Vehicle1  Vehicle2
103923    5594800   54114
105938    40547     1855442
103923    2588603   5659158
103923    54114     5594800
103923    5659158   2588603
105938    1855442   40547

There are no duplicates in any row, values are all unique. But every value in Vehicle1 exists in vehicle2. How would i get it so only one of each pair exists?

yes, it's only a,b and b,a pairs and there's no columns that don't look similar to this. — Edward Gorelik
– Edward Gorelik, Commented Dec 11, 2018 at 16:22

Slava Lenskyy · Accepted Answer · 2018-12-11 16:50:12Z

2

GREATEST and LEAST functions might help. DELETE ... USING syntax

DELETE
FROM t a USING
  ( SELECT location, 
           greatest(Vehicle1 , Vehicle2) as vehicle1, 
           least(Vehicle1 , Vehicle2) as vehicle2
   FROM t
   GROUP BY 1,2,3 HAVING COUNT(*) > 1 ) b
WHERE a.location = b.location
  AND a.Vehicle1 = b.Vehicle1
  AND a.Vehicle2 = b.Vehicle2;

edited Dec 11, 2018 at 16:50

answered Dec 11, 2018 at 16:31

Slava Lenskyy

4662 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Edward Gorelik Over a year ago

This looks like it works! Any chance you could explain what exactly is happening in this? I'm still newish to postgresql and am curious.

Slava Lenskyy Over a year ago

There is a couple of thing going on here. a) The subquery finds the duplicates. It does it by sorting two columns: vehicle1 will have the greatest of the two and vehicle2 will have the least. Now the rows would be identical (they will have same value for vehicle1 and vehicle2, so you can find the duplicates using having count(*) > 1 b) the delete query then uses all three fields as a primary key to delete the rows. It will leave the other one alive because values for vehicle1 and vehicle2 are switched. Hope this helps.

Edward Gorelik Over a year ago

Wow, so it becomes like an inter-column sort. Thanks for the explanation!

Collectives™ on Stack Overflow

Removing Duplicates From Multiple Unique Columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related