i haven't really touched much of PL/SQL before this project and my current knowledge of DBs is limited to SQLite, MySQL, PostgreSQL and other non sql DB technologies so bear with me on this.
First i started with a query that would get me the max difference in hours between two dates for each ID i had on that table. It goes something like this:
SELECT
id_trip as ID,
MAX(24 * (Trip.actual_arrival_date- (Trip.programmed_arrival_date))) as MAX_DELAY_HOURS
FROM Trip
GROUP BY ID
And that returns me something like this:
Results of first query
So i can say that i successfully went into the all the trips that exist for each ID and got the ones that have the maximum delay.
Now what i want to do after that is to join other types of information to that table, namely the actual date of the trip, the name of the departure spot and the name of the arrival spot. So i did something like this:
SELECT
internal_ID as external_ID,
programmed_date,
starting_airport.name as starting_airport,
destination_airport.name as destination_airport,
24 * (Trip.actual_arrival_date- (Trip.programmed_arrival_date)) AS external_delay
FROM Regular_flight
INNER JOIN Trip ON Regular_flight.ID = Trip.ID_Regular_flight
INNER JOIN Flight ON Regular_flight.ID_Flight = Flight.ID
INNER JOIN Airport starting_airport ON starting_airport.ID_IATA = Flight.ID_STARTING_AIRPORT
INNER JOIN Airport destination_airport ON destination_airport.ID_IATA = Voo.ID_DESTINATION_AIRPORT
INNER JOIN (
--this is the query that i built before--
SELECT
id_trip as internal_ID,
MAX(24 * (Trip.actual_arrival_date- (Trip.programmed_arrival_date))) as MAX_DELAY_HOURS
FROM Trip
GROUP BY ID
) ON internal_ID = external_ID
Order by external_ID;
And this actually returns something like this:
Results of second query
Now my problem is that while i have all the info i need there... i wanted to filter it out so that it only shows me the highest EXTERNAL_DELAY for each EXTERNAL_ID.
Usually i'd do a GROUP BY EXTERNAL_ID but since i'm selecting many things not just EXTERNAL_ID it won't actually execute the code. I've tried to do a GROUP BY with all the columns i'm selecting in the external query but then i have all the "combinations" between the external_ID and Programmed_date which is not what i'm looking for.
Basically from the 2nd query i want to reach something like this:
| EXTERNAL_ID | PROGRAMMED_DATE | STARTING_AIRPORT | DESTINATION_AIRPORT | EXTERNAL_DELAY |
| 1 | 16.07.08 | Aeroporto de Gatwick | Aeroporto Francisco Sa Carneiro | 744 |
| 2 | 16.08.08 | Aeroporto de Gatwick | Aeroporto Francisco Sa Carneiro | 0 |
| 3 | 16.08.09 | Aeroporto Francisco Sa Carneiro | Aeroporto Francisco Sa Carneiro | 744 |
And so on for each ID, so basically for each ID the the MAXIMUM delay found no matter what the date is.
I've been scratching my head for a few hours now and i'd like to have someone point me out in the right direction.
Appreciate any help i can get.