Why Max is throwing in below query in hive for alias

Question

Below query is perfect and is working with other databases. When this query is run then it is throwing error in input to Max is not recognized. When this query is ran individually then it is working.

SELECT country, COUNT(country)

FROM airports GROUP BY country

HAVING COUNT (country)=(
SELECT MAX(mycount)

FROM (
SELECT country, COUNT(country) as mycount

FROM airports
GROUP BY country));

Answer 1

Hi,

You can do one thing, just enclose column name with back tick. Check it works or not.

SELECT `country`, COUNT(`country`) FROM airports GROUP BY `country` HAVING COUNT (`country`)=(SELECT MAX(`mycount`) FROM (SELECT `country`, COUNT(`country`) as `mycount` FROM airports GROUP BY `country`));