HQL select rows with max

Question

I got this table:

+---+----+----+----+
|ID |KEY1|KEY2|COL1|
+---+----+----+----+
|001|aaa1|bbb1|ccc1|
|101|aaa1|bbb1|ddd2|
|002|aaa2|bbb2|eee3|
|102|aaa2|bbb2|fff4|
|003|aaa3|bbb3|ggg5|
|103|aaa3|bbb3|hhh6|
+---+----+----+----+

The Result must contain the rows with the highest ID if the columns key1 and key2 are equals.

+---+----+----+----+
|ID |KEY1|KEY2|COL1|
+---+----+----+----+
|101|aaa1|bbb1|ddd2|
|102|aaa2|bbb2|fff4|
|103|aaa3|bbb3|hhh6|
+---+----+----+----+

Since in HQL I can't do a subquery like:

select * from (select....)

How can I perform this query?

**SOLUTION**

Actually the solution were a little bit more complex so i want share it since the KEY1 and KEY2 were on an other table which join on the first table with two keys.

+-----+-------+-------+-------+
|t1.ID|t2.KEY1|t2.KEY2|t1.COL1|
+-----+-------+-------+-------+
|  001|   aaa1|   bbb1|   ccc1|
|  101|   aaa1|   bbb1|   ddd2|
|  002|   aaa2|   bbb2|   eee3|
|  102|   aaa2|   bbb2|   fff4|
|  003|   aaa3|   bbb3|   ggg5|
|  103|   aaa3|   bbb3|   hhh6|
+-----+-------+-------+-------+

I used this CORRECT query:

SELECT t1.ID, t2.KEY1, t2.KEY2, t1.COL1
FROM yourTable1 t1, yourTable2 t2
WHERE 
t1.JoinCol1 = t2.JoinCol1 and t1.JoinCol2=t2.JoinCol2 and
t1.ID = (SELECT MAX(s1.ID) FROM yourTable1 s1, yourTable2 s2
         WHERE 
         s1.JoinCol1 = s2.JoinCol1 and s1.JoinCol2=s2.JoinCol2 and
         s2.KEY1 = t2.KEY1 AND s2.KEY2 = t2.KEY2)

Answer 1

If we were writing this query to be run directly on a regular database, such as MySQL or SQL Server, we might be tempted to join to a subquery. However, from what I read here, subqueries in HQL can only appear in the SELECT or WHERE clauses. We can phrase your query as follows, using the WHERE clause to implement your logic.

The query will be:

SELECT t1.ID, t1.KEY1, t1.KEY2, t1.COL1
FROM yourTable t1
WHERE t1.ID = (SELECT MAX(t2.ID) FROM yourTable t2
               WHERE t2.KEY1 = t1.KEY1 AND t2.KEY2 = t1.KEY2)