CODEWITHSUNDEEP
sqlhivemax
TopCoder
TopCoder

Reputation: 4296

Hive: Finding max value in a group by

I have a hive table something like this:

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}';

I need to find out total clicks for a test_id and the last click date(max date in that group of test_id)

I am doing something like this 

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;

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 ?

Upvotes: 6

Views: 42601

Answers (1)

Shahbaz Chishty
Shahbaz Chishty

Reputation: 502

SELECT s.test_id,
       s.test_name,
       s.description,
       MAX(CAST(last_referred_click_date as DateTime)), 
       sum(t.clicks) as Total_Clicks
FROM test s
WHERE s.test_id=1
GROUP BY s.test_id,s.test_name,s.description 
ORDER BY clicks desc;

Upvotes: 2

Related Questions