rank based on counts (hiveql)

Question

I want to rank conversation ids by their count of occurrences, so the first occurance is ranked as 1, second is ranked as 2, third is 3 etc.

I am getting a syntax error so most likely something is off

select 
    conversationid, 
    rank() over (partition by conversationid order by count(*) desc) as rnk
  from my_table
  group by conversationid

Error while compiling statement: FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies. Underlying error: org.apache.hadoop.hive.ql.parse.SemanticException: line 7:54 Not yet supported place for UDAF 'count'

Which error do you get exactly? Please edit your question to include this important piece of information. Also, sample data and desired results would help understanding what you want to achieve. — GMB

GMB GMB · Accepted Answer · 2020-10-06T20:33:27

If you want to rank conversations by their count, then you don't want a partition by clause in the window function:

select conversationid, rank() over(order by count(*) desc) rnk
from mytable
group by conversationid

This assigns rank 1 to the most frequent conversation(s).

rank based on counts (hiveql)

1 Answers