Oracle – Select the Max from Two Max Values

countoracle

I need to find the airport(departure or arrival) that had the most passengers in the last year.

The tables are:

PASSENGER(id,name,surname)
TAKES(id_passenger,id_flight)
FLIGHT(id,departure_id,arrival_id,departure_date,arrival_date,id_plane)
AIRPORT(id,name,city,country)
PLANE(id,type,capacity)

What I think I need to do is:
Count the number of passengers each airport has and return that
but I do not know how to pick the one with the most passengers among those two.
Do I need to use nested select queries? Would it be useful to use something like 

WITH maxdep as 
  SELECT ( max passengers from departure airport)
maxar as SELECT  ( max passengers from arrival airport)
select max (val1, val2),  from ... ?

How do I return just one?

EDIT:
I was thinking of a case select from the two previous with statements.
Can that work?

WITH departures as (SELECT departure_id, count(id_passenger)  
    from passenger, takes, flight
    WHERE passenger.id=takes.id_passenger
    and takes.id_flight=flight.id
    and flight.departure_date between '01-JAN-2013' AND '01-JAN-2014'
    group by 1
    order by 2 desc
    where ROWNUM <2)

arrivals as (SELECT arrival_id, count(id_passenger) 
    from passenger, takes, flight
    WHERE passenger.id=takes.id_passenger
    and takes.id_flight=flight.id
    and flight.arrival_date between '01-JAN-2013' AND '01-JAN-2014'
    group by 1
    order by 2 desc
    where ROWNUM <2)
SELECT CASE 
        WHEN departures.count > arrivals.count THEN departures.departure_id  
        WHEN departures.count < arrivals.count THEN arrivals.arrival_id  
        ELSE departures.departure_id || 'has the same number of visitors as'|| arrivals.arrival_id   
        END;

I am sorry if this is messed up

Will this work?

Best Answer

Here's one possibility:

with departures(departure_id, cnt) as (
    select f.departure_id, count(t.id_passenger)
    from flight f
    join takes t
        on f.id = t.id_flight
    where year(departure_date) = ?
    group by f.departure_id
),
arrivals as (arrival_id, cnt) as (
    select f.arrival_id, count(t.id_passenger)
    from flight f
    join takes t
        on f.id = t.id_flight
    where year(arrival_date) = ?
    group by f.arrival_id
)
select position, max(cnt)
from (
    select arrival_id as position, cnt
    from arrivals
    union
    select departure_id as position, cnt
    from departures
) as t;
Related Question