title: Hive: Finding max value in a group by
body: <p>I have a hive table something like this:</p>
  
  <pre><code>create external table test(
    test_id string,
    test_name string,
    description string,
    clicks int,
    last_referred_click_date string
  )
  ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
  STORED AS TEXTFILE LOCATION  '{some_location}';
  </code></pre>
  
  <p>I need to find out total clicks for a test_id and the last click date(max date in that group of test_id)</p>
  
  <p>I am doing something like this </p>
  
  <pre><code>insert overwrite table test partition(weekending='{input_date}')
    select s.test_id,s.test_name,s.description,max(click_date),
      sum(t.click) as clicks
     group by s.test_id,s.test_name,s.description order by clicks desc; 
  </code></pre>
  
  <p>Does max() function works for strings? My click_date is of teh format'yyyy-mm-dd' and is a string data type? If not, what can I do here ? UDF ?</p>