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.

Sergey Zolotarev · Accepted Answer · 2023-06-06 18:37:59Z

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?
yes, for subquery in Criteria API refer stackoverflow.com/questions/4483576/…. 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).

Community · Accepted Answer · 2017-05-23 12:07:20Z

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, 2017 at 12:07

CommunityBot

11 silver badge

answered May 15, 2011 at 22:10

garnertb

9,62439 silver badges38 bronze badges

7 Comments

suicide Over a year ago

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?

garnertb Over a year ago

In your question you order one query by price and the other by purchases. Is there a purchases column on your Product table?

suicide Over a year ago

sorry my mistake, it should always be ordered by price. I corrected it.

garnertb Over a year ago

Change has been made, try it now.

DataNucleus Over a year ago

JPQL does not allow subqueries within aggregates in the SELECT clause. Some implementations may allow it, but losing portability if doing that

|

Collectives™ on Stack Overflow

Limit in subquery with JPQL

2 Answers 2

2 Comments

7 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

7 Comments

Linked

Related