To get Max count from Hibernate Group By

Question

How to implement below code in hibernate using criteria and projection :

select CUSTOMER_NO,count(*) as max_count
from table
group by CUSTOMER_NO
having count(*) in 
(
select 
max(count) 
from 
(
select count(*) as count,CUSTOMER_NO
from table
group by CUSTOMER_NO
) t1
)

Answer 1

Query can be change to: select CUSTOMER_NO, count(*) as count from temp.t group by CUSTOMER_NO order by count desc limit 1;

And criteria for the same is:

    criteria.setProjection(Projections.projectionList().add(Projections.groupProperty("CUSTOMER_NO")).add(Projections.rowCount(), "count"));
    criteria.addOrder(Order.desc("count"));
    criteria.setMaxResults(1);
    return criteria.list();