Need Alternative query in Apache Hive limit

Question

I need alternative query for the below query.

Select a.name,max(a.cnt) from (Select name,count(name) as cnt from candidate group by name) a group by a.name order by 2 desc limit 1;

drop table if exists candidate;

create external table candidate(name string) stored as textfile LOCATION '/user/cloudera/test/Exercise/candidate'

load data inpath '/user/cloudera/test/candidate' overwrite into table candidate;

Sample data:

raja

Anil

Giri

mahe

I need result which name is repeated more time. As per example above example

Anil repeated more.

To achieve the result I wrote the below query.But I am not satisfied with this query performance does anyone have alternative for this query?

Select a.name,max(a.cnt) from (Select name,count(name) as cnt from candidate group by name) a group by a.name order by 2 desc limit 1;

Thanks Venkadesan

Ben Watson Ben Watson · Accepted Answer · 2015-12-01T15:38:40

Select name, COUNT(*) AS cnt
FROM candidate 
GROUP by name
ORDER BY COUNT(*) DESC
LIMIT 1

Does it in one query and should run faster.