Distinct Route Pairs

Question

I'm trying to find a way to get distinctive pairs.

Suppose the table has 3 fields: id, city1 and city2.

Sample Data:
1, New York, Los Angeles
2, New York, Philadelphia
3, New York, Houston
4, Los Angeles, New York
5, Los Angeles, Houston
6, Houston, New York
7, Houston, Los Angeles

I would like the output to only include distinct pairs, regardless of the order of which column position they are in.

Sample Output:
New York, Los Angeles
New York, Philadelphia
New York, Houston
Los Angeles, Houston

Answer 1

Try

SELECT city1, city2 FROM YourTable WHERE city1 < city2 
    UNION SELECT city2, city1 FROM YourTable T1 WHERE city1 > city2

Make sure not to use the ALL keyword after the UNION so that the UNION will drop out duplicates from the result set.