Jun 6, 2016

SQL- avoid the logical duplicate


Query to avoid the logical duplicate records .(Technically these records are not duplicate ie: we can avoid records using the distinct keyword)
 
create table LOGICAL_DUP
( source      VARCHAR2(100),
  destination VARCHAR2(100),
  distance    NUMBER
) ;

insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) 
values ('AA', 'BB', 5000);
insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) 
values ('CC', 'DD', 1000);
insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) 
values ('FF', 'GG', 1500);
insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) 
values ('AA', 'CC', 3000);
insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) 
values ('GG', 'FF', 1500);
insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) 
values ('BB', 'AA', 5000); 

commit;
 
Output required is 
 
 AA  BB  5000
 AA  CC  3000
 CC  DD  1000
 FF  GG  1500
select min(a.source),max(a.destination),a.distance from LOGICAL_DUP a
where a.source  in (select b.destination from logical_dup b where 
b.source=a.destination)group by a.distance

union

select min(a.source),max(a.destination),a.distance from LOGICAL_DUP a
where a.source not  in (select b.destination from logical_dup b 
where b.source=a.destination)group by a.distance ;
 
OR 
 
select source,
       destination,
       distance
from (select source, 
             destination, 
             distance,
             row_number() over(partition by greatest(source, destination),
 least(source, destination) order by source, destination) rn
      from logical_dup)
where rn = 1 ;