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

Question

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!

Answer 1

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.

Answer 2

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

Answer 3

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