HQL/SQL select top 10 records based on count

hibernate, hql, mysql, sql

Solution

SQL query:

SELECT  c.Id, sub.POSTINGCOUNT
FROM CATEGORY c where c.Id IN
( 
    SELECT TOP 10 p.categoryId
    FROM POSTING p
    GROUP BY p.categoryId 
    order by count(1) desc
)

HQL:

Session.CreateQuery("select c.Id
        FROM CATEGORY c where c.Id IN
        ( 
            SELECT  p.categoryId
            FROM POSTING p
            GROUP BY p.categoryId 
            order by count(1) desc
        )").SetMaxResults(10).List();

http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/

Problem

I have 2 tables: ``` CATEGORY (id) POSTING (id, categoryId) ``` I am trying to write an HQL or SQL query to find top 10 Categories which have the most number of Postings. Help is appreciated.

Original source