Search code examples
javajpajpql

Is a IN subquery matching multiple columns possible in JPQL?

I have a SQL query I'm trying to convert to JPQL. The query is as follows : 

SELECT *
FROM MyTable
WHERE (myFirstColumn, mySecondColumn) IN (
    SELECT myFirstColumn, max(mySecondColumn)
    FROM MyTable
    GROUP BY myFirstColumn
)

My conversion attempt is straightforward :

select myObject
from MyObject as myObject
where (myObject.myFirstValue, myObject.mySecondValue) in (
    select subMyObject.myFirstValue, max(subMyOject.mySecondValue)
    from MyObject as subMyObject
    group by subMyObject.myFirstValue
)

MyObject is mapped to MyTable (using annotations).

If I understand the JPQL docs on the IN statement (http://openjpa.apache.org/builds/1.2.3/apache-openjpa/docs/jpa_langref.html#jpa_langref_in), and I'm really not sure I do, such a direct conversion isn't possible. Is there another way ?

Solution

  • In the end, I couldn't find a way and had Java do the heavy lifting (ie. sorting one the second value and finding the biggest). It's not as pretty but it works.