I have this table:
origin destination
new york seattle
new york chicago
new york portland
seattle new york
seattle chicago
chicago new york
I have to build a graph so I need to remove all the duplicated reversed pairs to have:
origin destination oneway
new york seattle 0
new york chicago 0
new york portland 1
seattle chicago 1
I already read this post: SQL -- Remove duplicate pairs but it's not useful for me because I have string filed.
Thanks
One method uses aggregation:
select origin, destination,
(case when exists (select 1
from t t2
where t2.origin = t.destination and t2.destination = t.origin
)
then 0 else 1
end) as one_way
from t
where origin < destination
union all
select origin, destination, 1
from t
where origin > destination;
An alternative method uses window functions:
select origin, destination, (cnt = 1)::int as one_way
from (select t.*,
count(*) over (partition by least(origin, destination), greatest(origin, destination)) as cnt
from t
) t
where origin < destination or
(origin > destination and cnt = 1);