I have a table of information about commercial airline flights, which includes, among other things, the city where a given flight originates (origin_city) and the flight time (actual_time).
I'm trying to understand why a (perhaps naively) simple query does not return the originating city for the flight with the longest flight time.
The following query:
SELECT FLIGHTS.origin_city, MAX( FLIGHTS.actual_time ) as [time]
FROM FLIGHTS
results in the error:
Column 'FLIGHTS.origin_city' is invalid in the select list because
it is not contained in either an aggregate function or the GROUP BY
clause.
The way I would think that SQL would interpret this query is that it would find the row that has the maximum flight time and then display the origin_city and the actual_time columns for that row.
Why doesn't that work?
If I leave the origin_city out of the SELECT, then the query runs fine:
SELECT MAX( FLIGHTS.actual_time ) as [time]
FROM FLIGHTS
If I group the flights by origin_city, then SQL doesn't have any issues pulling both the origin_city and the actual_time from each row.
SELECT FLIGHTS.origin_city, MAX( FLIGHTS.actual_time ) as [time]
FROM FLIGHTS
GROUP BY FLIGHTS.origin_city
Note that I am not looking for a query that works; I am trying to understand why my naive query at the beginning of the post doesn't work . . . :-P
SELECT FLIGHTS.origin_city, MAX( FLIGHTS.actual_time ) as [time]
FROM FLIGHTS
The way I would think that SQL would interpret this query is that it would find the row that has the maximum flight time and then display the origin_city and the actual_time columns for that row.
I find that interpretation silly. How do you propose that SQL handle these queries?
SELECT FLIGHTS.origin_city, MAX(FLIGHTS.actual_time) as [time],
MIN(FLIGHTS.actual_time)
FROM FLIGHTS;
Or:
SELECT FLIGHTS.origin_city, AVG(FLIGHTS.actual_time) as [time]
FROM FLIGHTS;
Not so obvious, eh? I suppose you could make some "special case" interpretation if there is one aggregation function and it is MIN() or MAX() but not any other. In fact, SQLite does that -- to the detriment of people who learn aggregation through that database.
Further, there is a real easy way to do what you want:
SELECT FLIGHTS.origin_city, FLIGHTS.actual_time
FROM FLIGHTS
ORDER BY FLIGHTS.actual_time DESC
OFFSET 0 ROW FETCH FIRST 1 ROW ONLY;