I have asked this question before but not able to get the link.
The question is:
I have flight table containing following values:
From_City To_City Fare
A B 50000
B A 50000
A C 60000
How to remove either A to B or B to A since it conveys the same meaning.
The Final output should be:
From_City To_City Fare
A B 50000
A C 60000
You could use the exists operator. Since you only want to remove one row of a pair of duplicates, you need to make an arbitrary decision which one to keep and which one to eliminate. In the following query I decided to keep the one with the lower from_city:
SELECT *
FROM flights a
WHERE NOT EXISTS (SELECT *
FROM flights b
WHERE a.from_city = b.to_city AND
a.to_city = b.from_city AND
a.fare = b.fare AND
b.from_city > b.to_city)
I would suggest:
select f.*
from flights f
where f.from_city < f.to_city
union all
select f.*
from flights
where f2.from_city > f.to_city and
not exists (select 1
from flights f2
where f2.from_city = f.to_city and
f2.to_city = f.from_city and
f2.fare = f.fare
);
Assuming the fares are always the same between any two cities no matter the direction, you could group by the "sorted" pair and get the maximum (or minimum, they are the same per assumption) fare.
SELECT CASE
WHEN from_city > to_city,
THEN to_city
ELSE from_city
END from_city,
CASE
WHEN from_city > to_city,
THEN from_city
ELSE to_city
END from_city,
max(fare) fare
FROM elbat
GROUP BY CASE
WHEN from_city > to_city,
THEN to_city
ELSE from_city
END,
CASE
WHEN from_city > to_city,
THEN from_city
ELSE to_city
END;
