CODEWITHSUNDEEP
sqlselectcasedistinct
user2330382
user2330382

Reputation: 19

SQL select distinct "from" and "to" Cities

Given below travel table, the from_city and to_city have duplicate entries.

from_city to_city distance
NYC BOS 300
BOS NYC 300
OKC BOS 600

Write a query to retrieve only unique combinations as below -

from_city to_city distance
NYC BOS 300
OKC BOS 600

Upvotes: 0

Views: 381

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

Assuming you have no other duplicates, you can use:

select t.*
from t
where t.from_city < t.to_city or
      not exists (select 1
                  from t t2
                  where t2.from_city = t.to_city and
                        t2.to_city = t.from_city
                 );

That is, select rows where they are ordered alphabetically. Or select rows where the inverse row does not exist.

Upvotes: 1

Related Questions