Limit in subquery with JPQL

Question

I'd like to get the average price of my top 100 products via JPA2. The query should look something like this (my sql is a little rusty):

select avg(price) from (
      select p.price from Product p order by p.price desc limit 100)

but that is not working at all. I also tried this:

select avg(p.price) from Product p where p.id = 
       (select pj.id from Product pj order by pj.price desc limit 100)

this is working up until the limit keyword.

I read that limit is not available in JPQL.

Any idea on how to do this? Criteria would also be fine.

score 5 · Accepted Answer · edited Jun 06 '23 at 18:37

LIMIT is not supported by JPQL. Below is the sample-code using Criteria-API.

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Double> criteriaQuery = builder.createQuery(Double.class);
Root<Product> productRoot = criteriaQuery.from(Product.class);
criteriaQuery.select(builder.avg(productRoot.get("price")));
criteriaQuery.orderBy(builder.desc(productRoot.get("price"));
Double average = (Double)entityManager.createQuery(criteriaQuery).setMaxResults(100).getSingleResult();

or

Double average = (Double)entityManager.createQuery("select avg(p.price) from Product p order by p.price").setMaxResults(100).getSingleResult();

If this doesn't work, then you have to go for executing two queries – selecting definitely ordered records & then averaging them.

Else, go for a native query if portability is not an issue. You can accomplish same using a single query as many RDBMSes support restricting the number of results fetched from a database.

Ok i got it to work using 2 queries. Neither Hsql nor H2 seem to like the criteria and the hsql as it uses order by using avg() at the same time. Is it possible to do a subquery with the Criteria API? — suicide, May 16 '11 at 21:50
yes, for subquery in Criteria API refer http://stackoverflow.com/questions/4483576/jpa-2-0-criteria-api-subqueries-in-expressions/4668015#4668015. But the problem is in restricting the results to be fetched in JPA for subquery. In hibernate it can be done by query.setFetchSize(int fetchSize). — Nayan Wadekar, May 19 '11 at 17:16

score 0 · Answer 2 · edited May 23 '17 at 12:07

0

SELECT AVG(SELECT PRICE FROM PRODUCT ORDER BY PRICE DESC LIMIT 100)

See this post regarding the JPQL LIMIT work around.

edited May 23 '17 at 12:07

Community

1
1

answered May 15 '11 at 22:10

garnertb

9,454
36
38

That does not work. It just gives me an SQL exception saying it is an invalid order by expression. I am using hsql by the way. Ok that work around would need 2 db queries right? – suicide May 15 '11 at 22:14
In your question you order one query by price and the other by purchases. Is there a purchases column on your Product table? – garnertb May 15 '11 at 22:17
sorry my mistake, it should always be ordered by price. I corrected it. – suicide May 15 '11 at 22:21
Change has been made, try it now. – garnertb May 15 '11 at 22:24
I switched to H2 as there was some SQL grammar error with hsql. but also H2 has a problem with the order by: org.h2.jdbc.JdbcSQLException: Column "PRODUCT0_.PRICE" must be in the GROUP BY list; SQL statement: select avg(cast(product0_.price as double)) as col_0_0_ from Product product0_ order by product0_.price desc limit ? [90016-154] – suicide May 15 '11 at 22:38
getting a hql syntax error. it seems that it cannot handle the sub query. The error occurs at the second select – suicide May 15 '11 at 23:20
2

JPQL does not allow subqueries within aggregates in the SELECT clause. Some implementations may allow it, but losing portability if doing that – DataNucleus May 16 '11 at 07:03

Limit in subquery with JPQL

2 Answers2

Linked

Related