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 ;
No comments:
Post a Comment