Hibernate version:
3.2

I'm in situation that I have to list items up ordered by children's count.
Let me say parent is 'User' and children is 'Order'.

This is basic one-to-many relation.
One user can have multiple orders.

I've already finished working on mapping file and generation POJO, etc.
Then how can I get the list which tells top 10 users who place orders the most?

This can be easily done by SQL, but no idea with HQL.
I tried something like this

select count(o) from User u left join Order o group by u

then got syntax error :(

Thanks in advance.

Can't this be easily solved?
Or am I not clear?

Instead of group by u use u.x(x is a column in u)

Try the below query

select u.username,count(o) from User u left join Order o group by u.username order by count(o) desc

Regards
Gokul

thanks

it works