Geek asks and answers

SELECT DISTINCT + matching values ​​from two columns = "unique"

I know that the name is spelled poorly, but I could not think of a better way to say it.

I am learning Ruby and am updating in MySQL. I use the historical completed flight list as a practice dataset with about 100,000 lines running. Each flight record includes origin and destination airports (origin and dest fields) and total flight distance (distance field).

As an exercise, I want to show the 10 longest routes sorted by distance. However, I want to consider each pair of endpoints as one route, regardless of which one is the source and which is the destination. So, for example, JFK-LAX and LAX-JFK should be considered as one route.

When I run the query:

SELECT DISTINCT distance, origin, dest FROM flights ORDER BY distance DESC LIMIT 10;

Of course, I get the following:

["2704", "BOS", "SFO"]
["2704", "SFO", "BOS"]
["2689", "BOS", "SJC"]
["2689", "SJC", "BOS"]
["2615", "LAX", "LIH"]
["2615", "LIH", "LAX"]
["2614", "HNL", "SAN"]
["2614", "SAN", "HNL"]
["2611", "BOS", "LAX"]
["2611", "LAX", "BOS"]

what I do not want. I want to say: "Choose the distance and end points of the 10 longest routes, regardless of whether the airports are source or destination."

, , , , LAX JFK = "JFKLAX". , , , .

SQL/MySQL?

+3
1

- GREATEST() LEAST() , . , DISTINCT .

SELECT DISTINCT
  distance,
  LEAST(origin, dest) AS endpoint1,
  GREATEST(origin, dest) AS endpoint2
FROM flights f
ORDER BY distance DESC LIMIT 10

sqlfiddle

, LEAST('BOS', 'SFO') 'BOS', GREATEST('BOS', 'SFO') 'SFO'. , , , DISTINCT .

+3

More articles:


All Articles