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.

Nayan Wadekar · Accepted Answer · 2011-05-16T17:34:03.397

'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 have to go for executing two queries - selecting definitely ordered records & then average them.

Else go for native query if portability is not an issue, can accomplish same using single query as many RDBMS supports restricting the number of results to be fetched from 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,250
33
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