I have a table as given below.

I want to select all records with a distinct combination of City1 and City2. Please note that A,B and B,A should be considered as same combination.

The desired output is as follows.

Thank you!

CodePudding user response：

If you KNOW that all rows with the same pair of cities have the same values for Fare and Dist, then the following should give you what you are looking for, even if there are more than 2 rows with the same pair of cities.

SELECT MIN(B.Fare) AS Fare, B.City1, B.City2, MIN(B.Dist) AS Dist
FROM (
  SELECT A.Fare, LEAST(A.City1, A.City2) AS City1, GREATEST(A.City1, A.City2) AS City2, A.Dist
  FROM FareTable A
  ) B
GROUP BY B.City1, B.City2
ORDER BY B.City1;

To check for bad data, where Fare or Dist are different, this shouldn't return any rows.

SELECT MAX(A.Fare) - MIN(A.Fare), LEAST(A.City1, A.City2) AS City1, GREATEST(A.City1, A.City2) AS City2, MAX(A.Dist) - MIN(A.Dist)
FROM FareTable A
GROUP BY City1, City2
HAVING (MAX(A.Fare) > MIN(A.Fare)) 
  OR (MAX(A.Dist) > MIN(A.Dist));

After MatBaille's answer, I thought I'd offer another variant which is neater than my original query and will only return one row for each unordered pair of cities, even if there exists 'bad data' in the table.

SELECT DISTINCT MIN(B.Fare) AS Fare, LEAST(B.City1, B.City2) AS City1, GREATEST(B.City1, B.City2) AS City2, MIN(B.Dist) AS Dist
FROM FareTable B
GROUP BY City1, City2
ORDER BY City1;

CodePudding user response：

Based on your sample data with two combinations of each possible you could try

select fare, min(city1) city1, max(city2) city2, dist
from t
group by fare, dist;

CodePudding user response：

Force the order to always be the same, then select only the distinct rows.

SELECT
  DISTINCT
  fare,
  IF(city1 < city2, city1, city2)  AS city1,
  IF(city1 < city2, city2, city1)  AS city2,
  dist
FROM
  your_table

Having noted the other answers, MySQL includes LEAST() and GREATEST(), allowing tidier code...

SELECT
  DISTINCT
  fare,
  LEAST(   city1, city2)  AS city1,
  GREATEST(city1, city2)  AS city2,
  dist
FROM
  your_table