How to remove reverse duplicates from a SQL query

Question

Context: I have a select query where I have have link between devices. Some links have "reverse duplicates" that I would like to get rid of in the select query.

I already searched for similar questions especially this one : Remove reverse duplicates from an SQL query

But the solutions here works because it has only duplicates with an order but it isn't applicable in my case.

Here is a fiddle of the query with data examples to be easier to test

http://sqlfiddle.com/#!17/214e6/7

insert into link (ip_src, ip_dst) values ('192.168.0.1','192.168.0.2');
insert into link (ip_src, ip_dst) values ('192.168.0.1','192.168.0.3');
insert into link (ip_src, ip_dst) values ('192.168.0.5','192.168.0.4');
insert into link (ip_src, ip_dst) values ('192.168.0.7','192.168.0.8');
insert into link (ip_src, ip_dst) values ('192.168.0.8','192.168.0.7');

Desired result:

'192.168.0.1', '192.168.0.2'  
'192.168.0.1', '192.168.0.3'  
'192.168.0.5', '192.168.0.4'  
'192.168.0.7', '192.168.0.8'  

Answer 1

If you don't care about the ordering in the final result set, you can do:

select distinct least(ip_src, ip_dst) as ip_src, greatest(ip_src, ip_dst) as ip_dst
from link ;

Note: this can result in pairs not in the original table.

If you do care about ordering:

select ip_src, ip_dst
from link l
where ip_src <= ip_dst
union all
select ip_src, ip_dst
from link l
where ip_src > ip_dst and
      not exists (select 1 from link l2 where l2.ip_src = l.ip_dst and l2.ip_dst = l.ip_src);

Note: This uses union all, so it does not remove duplicates. You can use union to remove duplicates.

Answer 2

SELECT *
FROM link l
WHERE NOT EXISTS (
        SELECT * from link nx 
          -- The condition below is true for both the twins
        WHERE nx.ip_src = l.ip_dst AND nx.ip_dst = l.ip_src
          -- this is true for only one of the twins
        AND nx.ip_src < nx.ip_dst
        );