Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

HQL/SQL select top 10 records based on count

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.

like image 737
user879220 Avatar asked Jun 19 '12 17:06

user879220


2 Answers

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/

like image 137
Baz1nga Avatar answered Sep 29 '22 06:09

Baz1nga


In SQL you can do this:

SELECT c.Id, sub.POSTINGCOUNT
FROM CATEGORY c 
INNER JOIN 
( 
    SELECT p.categoryId, COUNT(id) AS 'POSTINGCOUNT'
    FROM POSTING p
    GROUP BY p.categoryId
) sub ON c.Id = sub.categoryId
ORDER BY POSTINGCOUNT DESC
LIMIT 10
like image 21
Mahmoud Gamal Avatar answered Sep 29 '22 07:09

Mahmoud Gamal