CODEWITHSUNDEEP
sqloracle-database
sdominic
sdominic

Reputation: 21

How to remove logical duplicates in oracle

How can we remove logical duplicates from the below table ( ie when the from and to is same stations either of 2 rows should be returned)

 from_station| to _station| distance  
 delhi   |      mumbai   |   5000  
 mumbai  |      delhi    |  5000  
 delhi   |      cochin   |   3000  
 cochin  |      delhi    |   3000

Thanks in advance

Upvotes: 1

Views: 878

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44911

select   distinct

         least    (from_station,to_station)
        ,greatest (from_station,to_station)
        ,distance 

from     t

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If you just want to return results, then you can do:

select t.*
from t
where t.from_station < t.to_station
union all
select t.*
from t
where t.from_station > t.to_station and
      not exists (select 1
                  from t t2
                  where t2.from_station = t.to_station and t2.to_station = t.from_station
                 );

An alternative mechanism uses aggregation:

select least(t.from_station, t. _station) as from_station,
       greatest(t.from_station, t. _station) as to_station,
       avg(distance)
from t
group by least(t.from_station, t. _station),
         greatest(t.from_station, t. _station);

However, this can introduce rows that are not in the original data (if only one row exists between two city pairs).

Upvotes: 1

Related Questions