CODEWITHSUNDEEP
sqlpostgresql
user1896659
user1896659

Reputation:

How to SELECT DISTINCT from values of two columns

I'd like to get the distinct values that occur in either of two columns (or in both). Not the grouping of the columns, but the individual values themselves.

For example, querying a table of air travel routes with columns ORIGIN and DESTINATION. I'd like to get the list of all airports, regardless of whether it is an origin or destination.

SFO, LGA
SFO, LAX
JFK, LAX
JFK, SFO
LAX, SFO
LAX, LGA

Should return

LAX, SFO, LGA, JFK

I am using Postgres 9.6 if it makes any difference.

Upvotes: 1

Views: 58

Answers (3)

Nomis
Nomis

Reputation: 1

look into using aggregate functions (<,>) on the both columns

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Just use union:

select origin as airport
from routes
union  -- used on purpose to remove duplicates
select destination
from routes;

Upvotes: 4

user1896659
user1896659

Reputation:

While writing my question, I managed to solve it, but I'm not sure if this is the most optimal case.

SELECT DISTINCT orig AS airport_code
FROM (
  SELECT origin FROM airport_routes
  UNION ALL
  SELECT destination FROM airport_routes
);

Upvotes: 0

Related Questions