Select row with group by and max in JPQL

Question

I have below structure. Not in one table but just to keep it simple:

Press       | Batch        | Start time
-------------------------------------------------
PRESS_1     | 2051         | 2017-05-18T00:00:00
PRESS_1     | 2052         | 2017-05-19T00:00:00
PRESS_2     | 2053         | 2017-05-20T00:00:00
PRESS_2     | 2054         | 2017-05-16T00:00:00
PRESS_3     | 2055         | 2017-05-12T00:00:00
PRESS_3     | 2056         | 2017-05-18T00:00:00
PRESS_3     | 2057         | 2017-05-19T00:00:00

I want to get the last planned batch for each press. The result should be:

Press       | Batch        | Start time
-------------------------------------------------
PRESS_1     | 2052         | 2017-05-19T00:00:00
PRESS_2     | 2053         | 2017-05-20T00:00:00
PRESS_3     | 2057         | 2017-05-19T00:00:00

However, when I group by press and also want to select the batch. I am obliged to put the batch into the 'Group By' clause.

Select x.Press, x.Batch, max(x.StartTime)
From myTable x
Group by x.Press

How can this be achieved using JPQL?

Answer 1

as you want to select entry with max start_time of each press, you use something as mentioned below as well -

Select x.Press, x.Batch, x.StartTime
From x 
where (press,starttime) in 
(select press, max(starttime) 
from x group by press)

lets give a try and please let me know.