CODEWITHSUNDEEP
hadoophivemapreducehiveql
dv akhil
dv akhil

Reputation: 93

counting desc in hive mapreduce

I have a table in hive which consists of

questionid,questiontag,answerID,userIDofanswerer

I need Top 10 most commonly used tags in this data set.

I tried :

select count(questionID),questiontag from table GROUP BY tags;

but how do I order it by Count(questionID)

Upvotes: 1

Views: 56

Answers (2)

Arunakiran Nulu
Arunakiran Nulu

Reputation: 2099

try below

select count(questionID) as cnt,questiontag from table GROUP BY questiontag
order by cnt desc limit 10;

Upvotes: 0

leftjoin
leftjoin

Reputation: 38335

In the query below ORDER BY cnt DESC LIMIT 10 will select top 10 most commonly used tags:

    SELECT count(questionID) cnt ,
           questiontag 
      FROM table 
  GROUP BY questiontag 
  ORDER BY cnt DESC 
  LIMIT 10;

count(*) will count all rows including NULL questionID

count(questionID) will count only rows where questionID is not NULL

Upvotes: 1

Related Questions