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.
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/
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With