JPQL with subquery to select max count

Question

I'm trying to write a jpql query to select the user with the most comments. If two users have the same number of comments I want to select both.

I tried this, something like this:

SELECT
  c.user, COUNT(c.id) as commentCount 
FROM 
  Comment c
WHERE
  commentCount = (SELECT MAX(SIZE(user.comments)) FROM User user)
GROUP BY 
  c.user

and this:

SELECT
  c.user
FROM 
  Comment c
GROUP BY 
  c.user
HAVING
  COUNT(c) = (SELECT MAX(SIZE(user.comments)) FROM User user)

Neither approach works. What do I need to do here?

When you say did not work, you mean it selected only one user when two users had same # of comments? — Victor, Apr 11 '13 at 19:51
Can you try: select * from ( SELECT c.user, COUNT(c.id) as commentCount FROM Comment c)s1 where s1.commentCount = (select max (s2.coomentCount) from SELECT c.user, COUNT(c.id) as commentCount FROM Comment c) s2 — Victor, Apr 11 '13 at 20:08
I always get a "could not execute query" because it is somehow misformed. So in the first place I get "Unknown column "jobsCount" in where clause" and in the second that the group by is invalid (when I add the HAVING part). But I'm not aware of how wo write a working version. Already tried many different versions. — Roland Schütz, Apr 11 '13 at 20:09
The query results in: unexpected token: * near line 1, column 8 So I have replaced it with s1 and then I got: unexpected token: ( near line 1, column 16 — Roland Schütz, Apr 11 '13 at 20:15
How about:select * from ( SELECT c.user, COUNT(c.id) as commentCount FROM Comment c)s1 where s1.commentCount = (select max (s2.coomentCount) from (SELECT c.user, COUNT(c.id) as commentCount FROM Comment c) s2 ) — Victor, Apr 11 '13 at 20:19
I believe JPQL doesn't support star!? You query with the replaced * results in: unexpected token: ( near line 1, column 16 — Roland Schütz, Apr 11 '13 at 20:35

score 6 · Answer 1 · edited Sep 18 '16 at 17:36

6

Here is a solution:

SELECT
  u
FROM 
  User u
WHERE
  u.comments.size = (SELECT MAX(u2.comments.size) FROM User u2)

edited Sep 18 '16 at 17:36

Robert Longson

118,664
26
252
242

answered Mar 27 '14 at 21:23

stoefln

14,498
18
79
138

Thanks for this answer! – Tim Hovius Sep 20 '17 at 08:04

score 0 · Answer 2 · edited Sep 18 '16 at 17:37

0

This should work if you are using Oracle:

select u from User u where size(u.comments) = (
    select max(count(c.id)) 
    from User u2 inner join u2.comments c 
    group by u2.id
)

But MySQL and SQL Server do not support nested aggregate functions, max(count(c.id)) in this case. It is suggested to use a subquery, but with HQL you cannot have subqueries in a from clause. So I suggest you do this manually, i.e. load all users:

select u, size(u.comments)
from User u

and loop through the list.

edited Sep 18 '16 at 17:37

Robert Longson

118,664
26
252
242

answered Apr 11 '13 at 22:39

lunr

5,159
4
31
47

Thanks for the answer. Sadly I'm using MySQL and I only can create one named query statement. Any other tips? – Roland Schütz Apr 11 '13 at 22:44
You can use a native query. – lunr Apr 11 '13 at 22:50

score 0 · Answer 3 · answered Oct 15 '21 at 16:09

For any others coming here and wanting to select a max(count()) in jpql and doesn't have an array, (like in the question the comments) take following jpql code into consideration:

select e.city 
from Employees e
group by e.city 
having count(e.id) >= All(select count(e) from Employees  e group by e.city)

full example in a JPA Repository:

@Query(value = "select e.city from Employees e group by e.city " +
        "having count(e.id) >= All(select count(e) from Employees  e group by e.city)")
public List<Cities> findCityByMaxEmployeeCount();

JPQL with subquery to select max count

3 Answers3