HQL subqueries replacement

Question

how can I replace this subquery in HQL (as HQL doesn't support subqueries):

SELECT l.aUser, count(l.aUser) 
FROM LifeTable l 
WHERE l.aUser IS NOT NULL
GROUP BY l.aUser 
HAVING count(l.aUser) = 
(SELECT min(cnt) 
 FROM (SELECT count(l.aUser) cnt 
       FROM LifeTable l 
       WHERE l.aUser IS NOT NULL 
       GROUP BY l.aUser) a)

This query should select the aUser(s) with minimum appearences in LifeTable. Thanks!

Answer 1

I think you can use all:

SELECT l.aUser, count(l.aUser) 
FROM LifeTable l 
WHERE l.aUser IS NOT NULL
GROUP BY l.aUser 
HAVING COUNT(*) <= ALL (SELECT count(*) as cnt 
                        FROM LifeTable l2 
                        WHERE l2.aUser IS NOT NULL AND
                              l2.aUser = l.aUser
                       );

And one slight correct. HQL does support subqueries, just not in the FROM clause.