Re: Top 10 airports referenced from a table of flights

[Jani Patokallio]

Thanks for the suggestions!

Rick James Wrote:
-------------------------------------------------------
> > select apid, count(*) as ct
> from (
> select src_apid as apid
> from flights
> UNION
> select dst_apid as apid
> from flights
> ) x
> group by apid
> order by count(*) desc
> limit 10;

This one worked after a small tweak: UNION eliminates duplicates by default, but "UNION ALL" keeps them and gives the right answer. It still does the temporary table and filesort, but this time only on the small airports table, so it's *much* faster -- under 1 second!

> select x.apid, sum(x.ct) as ct
> from ( select src_apid as apid, count(*) as ct
> from flights
> UNION
> select src_apid as apid, count(*) as ct
> from flights ) x
> group by apid
> order by ct desc
> limit 10;

Couldn't get this to work...

ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

...but fortunately it doesn't matter anymore. Thanks again!

Cheers,
-j.