Getting unique lanes from transportation table-CodePudding

I have a sql table named Transportation with following structure-

Date         Origin      Destination  Trailer_Count
2020/01/01   Chicago     Atlanta      5
2020/02/10   Chicago     Atlanta      2
2020/02/25   Atlanta     Chicago      3
2020/03/02   LA          Phoenix      6
2020/04/17   Phoenix     LA           6

How can I get unique lanes from above table. For reference, in above table there are 2 unique lanes (Chicago <> Atlanta, LA<> Phoenix)

I can't do select distinct origin, destination from transportation since it can return multiple records for the same lane.

CodePudding user response：

you can do something like

select distinct city1, city2 from 
(
select 
case when origin < destination then origin else destination end as city1,
case when origin < destination then destination else origin end as city2
from 
Transportation ) T