Geek Answers Handbook

JPQL with a subquery to select the maximum quantity

I am 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)

None of the approaches work. What do i need to do here?

+6
source share
2 answers

Here is the solution:

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

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 the from clause. Therefore, I suggest you do it manually, i.e. Download all users:

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

and scroll through the list.

0
source

More articles:


All Articles