Get max number of airports in a country

[#1]

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;

[#2]

@Abhijnan Kundu

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

[#3]

@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

[#4]

@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)));

[#5]

@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);

[#6]

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);

[#7]

@Abhijnan Kundu,

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

[#8]

output.jpg

[#9]

@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

[#10]

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.