CODEWITHSUNDEEP
sqlpostgresql
kman3
kman3

Reputation: 13

How to delete duplicates (not distinct)

I want to select the airports from routes, the thing is that the airport pairs are shown 2 times:

results

I want to show only the half (for ex. only BZK DME and not BZK DME and DME BZK)

SELECT 
   departure_airport, 
   arrival_airport, 
   departure_city, 
   arrival_city, 
   count (departure_airport) as sum
FROM 
    routes
GROUP BY
    departure_airport, 
    arrival_airport, 
    departure_city, 
    arrival_city
HAVING  
    count (departure_airport) > 2;

Upvotes: 0

Views: 81

Answers (2)

shashank padelkar
shashank padelkar

Reputation: 1

You can use Row_Number() for Selecting particular Combinations of arrival airport and departure airport

(select departure_airport, arrival_airport, departure_city, arrival_city, 
count (departure_airport) as sum,row_number()(order by departure_airport,arrival_airport) as row
from routes
group by departure_airport, arrival_airport, departure_city, arrival_city
having count (departure_airport) > 2
 )where row=1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Just use <:

select r.*
from routes r
where departure_airport > arrival_airport;

This assumes that all routes have exact pairs. This is suggested by your question.

Upvotes: 1

Related Questions