Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Get max number of airports in a country

Get max number of airports in a country

New Contributor

Hi,

I have a table, "airports" with below schema in Hive

airport_id string

name string

city string

country string

iata_faa string

icao string

latitude string

longitude string

altitude string

timezone string

dst string

tz string

I want to find the country with max number of airports. I have created below script that is giving me the max number of airport in a country but I am not able to print the country name along with it. Can I get a help pls?

select max(c.airportcount) from

(select count(airport_id) as airportcount,country from airports group by country) c;

9 REPLIES 9

Re: Get max number of airports in a country

New Contributor

@Abhijnan Kundu

select count(airport_id) as airportcount, country from airports group by country order by airportcount desc Limit 1;

Re: Get max number of airports in a country

@Abhijnan Kundu,

You can use this query which will return the country with max no of airports. It will return also multiple countries if they have same no of airports(max airports)

select c.countryName, c.airportcount 
from 
(select d.country as countryName, count(*) as airportcount from airport d group by d.country ) c 
join 
(select max(f.airportcount) as countmax from
(select cnt.country as countryName, count(*) as airportcount from airport cnt group by cnt.country ) f) g 
where c.airportcount = g.countmax;

You can also use solution mentioned by @Nde Gerald Awa , but it will not handle if more than 1 country has same no of max aiports.

Thanks,

Aditya

Re: Get max number of airports in a country

Expert Contributor

@Aditya Sirna

Could we do that?

select c.countryName, c.airportcount
from (select d.country as countryName, count(*) as airportcount from airport d group by d.country ) c
where c.airportcount = (select max(f.airportcount) from (select count(*) from airport cnt group by cnt.country)));

Re: Get max number of airports in a country

@Yassine,

We cannot run exactly the same query which you have mentioned . I have modified it a bit

select c.countryName, c.airportcount
from (select d.country as countryName, count(*) as airportcount from airport d group by d.country ) c where c.airportcount IN (select max(f.airportcount)  from (select  count(*) as airportcount from airport cnt group by cnt.country)f);

Re: Get max number of airports in a country

New Contributor

Thanks guys for the help. I have used below script. The only modification I have done is, I have removed alias d for airports table. Can any one please advise if keeping alias gives any advantage even though I am getting same result? @Aditya Sirna @Yassine @Nde Gerald Awa

select c.countryName, c.airportcount

from (select country as countryName, count(*) as airportcount from airports group by country ) c

where c.airportcount IN (select max(f.airportcount) from (select count(*) as airportcount from airports cnt group by cnt.country)f);

Re: Get max number of airports in a country

@Abhijnan Kundu,

Can you please paste the output/screenshot. This should work

Re: Get max number of airports in a country

New Contributor

Re: Get max number of airports in a country

@Abhijnan Kundu,

For me looks like something is retured but not visible. Can you try redirecting the output to a file and check the output

Re: Get max number of airports in a country

New Contributor

I have to check how to re-direct output to a file. The output is generating the count of airports only. It is not showing country due to data issues.