HQL / SQL selects the top 10 records by counting

Question

I have 2 tables:

CATEGORY (id) POSTING (id, categoryId) 

I am trying to write an HQL or SQL query to find the top 10 categories that have the most posts.

Help is appreciated.

Answer 1

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/

Answer 2

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 

Answer 3

SQL may look like:

 SELECT c.* from CATEGORY c, (SELECT count(id) as postings_count,categoryId FROM POSTING GROUP BY categoryId ORDER BY postings_count LIMIT 10) d where c.id=d.categoryId 

This output can be mapped to a Category object.

Answer 4

I know this is an old question, but I got a satisfactory answer.

JPQL:

 //the join clause is necessary, because you cannot use p.category in group by clause directly @NamedQuery(name="Category.topN", query="select c, count(p.id) as uses from Posting p join p.category c group by c order by uses desc ") 

Java:

 List<Object[]> list = getEntityManager().createNamedQuery("Category.topN", Object[].class) .setMaxResults(10) .getResultList(); //here we must made a conversion, because the JPA cannot order using a non select field (used stream API, but you can do it in old way) List<Category> cats = list.stream().map(oa -> (Category) oa[0]).collect(Collectors.toList());