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

score 0 · Answer 1 · answered Dec 01 '15 at 15:38

0

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.

answered Dec 01 '15 at 15:38

Ben Watson

1 Answers1