ashwini February 2016

Not getting results for Top n records from group in hive

I have one table table1 from that i need to show top 5 OS based on the highest counts i need to group them on three columns os,device,model

When i executed below query I didnt get any result. Whats wrong in the below query?

SELECT * FROM
     (SELECT distinct os, count,
     DENSE_RANK () OVER (PARTITION BY os ORDER BY count DESC)rn
     FROM (Select os ,device,model,SUM(cal_count) as count
     from table1
     group by os,device,model)b ) a
     WHERE a.rn = 5
     ;

Answers


Gordon Linoff February 2016

I would write the query like this:

select t.*
from (select os, device, model, SUM(cal_count) as cnt,
             dense_rank() over (partition by os order by sum(cal_count) desc) as seqnum
      from table1
      group by os, device, model
     ) t
where seqnum <= 5;

As I understand your query, it would only return the 5th row for each group. Perhaps there is some sort of error.

Post Status

Asked in February 2016
Viewed 2,702 times
Voted 9
Answered 1 times

Search




Leave an answer