Question: 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

Answer: 
```
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;

```
This might seem as though it has a simple answer... however... the simple answer as to to why your first query can't give you what you want is that you never told it to group on the FLIGHTS.origin\_city and so it doesn't know what to do with that field. You added an Aggregate function of MAX but you also added another field... so now it's not only looking at FLIGHTS.actual\_time but also FLIGHTS.origin\_city and so now when we group the FLIGHTS.origin\_city the query knows you are looking to see the MAX actual\_time per each origin\_city.

The query doesn't know which city you are needing a MAX(FLIGHTS.actual\_time). If you want to know a specific origin\_city and its MAX actual\_time you can add a filter to the WHERE clause to specify which origin\_city you want to see... otherwise by adding the origin\_city to the GROUP BY Clause this now tells the query you wish to see the MAX actual\_time per each city.

If it's not added to the GROUP BY clause the SQL doesn't know how to use MAX.