I need to do something like retrieve the top 10 selling products from each category, or the lowest cost items from each category (by category code) using a combination of I'd guess criteria or straight HQL. I am using MySQL.

I've scoured the documentation. HQL does not support LIMIT or ROWNUM. I'd assumed, I need to write a correlated subquery that queries the top 10 products in a given category and then qualifies the product based on the subquery.

What is the best way to go about doing something like this Hibernate?

Any suggestions appreciated.