Question 21676 in Mysql Question posted in
The official MySQL documentation can be found here.

How to select records with distinct combination of 2 columns in MySQL

I have a table as given below.

Fare City1 City2 Dist
100 A B 10
100 B A 10
200 A C 20
150 B D 15
150 D B 15

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.

Fare City1 City2 Dist
100 A B 10
200 A C 20
150 B D 15

Thank you!

Tags:

3

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    My original answer to this was too convoluted so I’ve removed it. See MatBaille’s query to give the correct answer with neat SQL provided there is no ‘bad data’. This question is a good advertisement for working with normalised data because there are too many alternative ways to handle ‘bad data’.

    To check for bad data, where Fare or Dist are different on different rows for the same unordered pair of cities, this shouldn’t return any rows.

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

    If it does, then the application reading from the SELECT DISTINCT… query will have to decide how to handle multiple rows with the same pair of cities.

    Login or Signup to reply.
  3. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search