HIVE/HiveQL Get Max count

Question

Sample Data

   DATE      WindDirection

   1/1/2000  SW
   1/2/2000  SW
   1/3/2000  SW
   1/4/2000  NW
   1/5/2000  NW

Question below

Every day is unqiue, and wind direction is not unique, So now we are trying to get the COUNT of the most COMMON wind direction

select w.wind_direction as most_common_wd
from (
       select wind_direction, count(*) as cnt
       from weather 
       group by wind_direction 
       order by cnt desc
) w
limit 1;

Currently this query works, however it outputs the count of ALL count, i am only interested in the count of each type of wind direction, it outputs south and outputs 170000 but the answer is only 10,000.

I am aware something is wrong the way count is being used, i think it have to specify an alias and do count by specfic wind_direction but i can't translate that into syntax

Answer 1

Looks like you want the most common wind direction from your data

select wind_direction, count(*) as cnt
from weather 
group by wind_direction 
order by cnt desc
limit 1;

If there are multiple wind direction with the same max count then get the max count and use it in the having clause to get the most common wind direction

select wind_direction 
from weather 
group by wind_direction 
having count(*) = ( select max(c) from 
                    (
                       select wind_direction,count(*) c 
                       from weather 
                       group by wind_direction 
                    ) a
                  )