CODEWITHSUNDEEP
group-byhqlhive
Judking
Judking

Reputation: 6371

How to order by count desc in each group in a hive?

Here's the HQL:

select A, B, count(*) as cnt from test_table group by A, B order by cnt desc;

The sample output is as follows:

a1 | b1 | 5
a2 | b1 | 3
a1 | b2 | 2
a2 | b2 | 1

But what I want is to do the order by in each group of A, and the intended output is like:

a1 | b1 | 5
a1 | b2 | 2
a2 | b1 | 3
a2 | b2 | 1

Could anyone can give me some idea how to resolve this problem in just one HQL? Thanks a lot!

Upvotes: 13

Views: 41377

Answers (3)

dimamah
dimamah

Reputation: 2903

select A, B, count(*) as cnt 
from test_table 
group by A, B 
order by A, cnt desc;

Upvotes: 26

androidbeginner
androidbeginner

Reputation: 61

select A, B, count(*) as cnt from test_table group by A, B order by A asc, B asc, cnt desc;

Upvotes: 0

Mukesh S
Mukesh S

Reputation: 2886

Try this query:

If you want only order of A then:

select A, B, count(*) as cnt from test_table group by A, B order by A asc;

If you want order of A and B then:

select A, B, count(*) as cnt from test_table group by A, B order by A asc,B asc;

Hope this helps.

Upvotes: 1

Related Questions