SQL уникальность пары

Question

есть такой запрос :

SELECT DISTINCT departure_city, arrival_city
FROM routes r
JOIN aircrafts a ON r.aircraft_code = a.aircraft_code WHERE a.model = 'Boeing 
777-300'
ORDER BY 1;

ответ:

как можно модифицировать запрос, чтоб каждая пара повторялась только 1 раз

т.е нужно получить в ответ:

Answer 1

SELECT 
DISTINCT 
LEAST(departure_city, arrival_city), 
GREATEST(departure_city, arrival_city)
FROM routes r 
JOIN aircrafts a 
ON r.aircraft_code = a.aircraft_code 
WHERE a.model = 'Boeing 777-300'                          
ORDER BY 1;

Answer 2

SELECT DISTINCT departure_city, arrival_city
  FROM routes r
  JOIN aircrafts a ON r.aircraft_code = a.aircraft_code
  WHERE a.model = 'Боинг 777-300'
      AND departure_city <> arrival_city
      AND arrival_city < departure_city;

БЛОГ НА HUSL

SQL уникальность пары

Ответы (2 шт):