JPQL Selecting Record with Max Value
hibernate, hql, jpa, jpql
Solution
No, it's not possible to generate the above SQL query with HQL. The documentation says:
Note that HQL subqueries can occur only in the select or where clauses.
Problem
I'm trying to select the record that has the max value for a particular column and here's what I have so far: ``` select o from Order o where o.orderNumber = :orderNumber and o.version = (select max(o.version) from Order o where o.orderNumber = :orderNumber) ``` This seems to be generating the SQL: ``` SELECT * FROM Order WHERE ordernumber = :orderNumber AND orderversion = (SELECT Max(orderversion) FROM order WHERE ordernumber = :orderNumber); ``` I think the following SQL would 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; ``` Is there anyway I can express this in JPQL? (Most answers online for questions related to selecting record with max value on a particular field seem to suggest the above JPQL I had presented...)