Geek asks and answers

JPQL Choosing a record with a maximum value

I am trying to select the record that has the maximum value for a specific column, and here is what I still have:

select o from Order o 
    where o.orderNumber = :orderNumber
    and o.version =
        (select max(o.version) from Order o where o.orderNumber = :orderNumber)

It seems to be generating SQL:

SELECT *
FROM   Order
WHERE  ordernumber = :orderNumber
       AND orderversion = (SELECT Max(orderversion)
                           FROM   order
                           WHERE  ordernumber = :orderNumber);

I think the following SQL will be more efficient:

SELECT *
FROM   order ord
       INNER JOIN (SELECT ordernumber,
                          Max (version) AS version
                   FROM   order
                   WHERE  ordernumber = :ordernumber
                   GROUP  BY ordernumber) mx
         ON ord.ordernumber = mx.ordernumber
            AND ord.version = mx.version;

Anyway, can I express this in JPQL?

(Most of the answers on the Internet regarding the selection of the record with the maximum value in a certain field seem to indicate the JPQL presented above, which I presented ...)

+3
source share
1 answer

No, it is not possible to create the aforementioned SQL query using HQL. The documentation says:

Please note that HQL subqueries can only occur in select or where articles.

+7

More articles:


All Articles