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?

@Veljko89 as you can see for PRESS_2 this is not always the case. — Anonymoose, May 18 '17 at 12:18
This question has been answered numerous times here in SO. Please try to do some research before posting a question. — Giorgos Betsos, May 18 '17 at 12:19
@GiorgosBetsos It was quite hard to find the right words to google this. But please keep the post open as this considers JPQL which is very much more limited than the duplicate post you refer to. Also that particular case is not the same principle. — Anonymoose, May 18 '17 at 12:21
I think you also mentioned about a SQL solution in the original version of your post. Never mind, I will vote for re-opening the question. — Giorgos Betsos, May 18 '17 at 13:55

score 5 · Answer 1 · answered May 18 '17 at 12:19

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.

ScaisEdge · Answer 2 · 2017-05-18T12:40:19.400

1

you could use an inner join with the aggregated result for grouping

select * from my_table as t1
inner join  (
select Press, max(start_time) my_time
from my_table 
group by Press ) t2 on t1.Press = T2.Press and t2.my_time = t1.start_time

or avoiding join

select * from my_table as t1
where (t1.Press, t1.start_time) in (
select Press, max(start_time) 
from my_table 
group by Press )

OR if you ORM is limited you could create a proper view and then use the view name as a table avoiding the subquery

edited May 18 '17 at 12:40

answered May 18 '17 at 12:17

ScaisEdge

131,976
10
91
107

Unfortunately, using subqueries in the join class is not allowed in JPQL – Anonymoose May 18 '17 at 12:26
is allowed the use of tuple ..? anyway answer updated with tuple – ScaisEdge May 18 '17 at 12:27
appearently also no tupples :( It is very limited because it's not native. – Anonymoose May 18 '17 at 12:35
the batch number is concorde with the start_time? .. almost in the same press? – ScaisEdge May 18 '17 at 12:38
I have update the answer adding a simple suggestion if you ORM framework is limitated .. – ScaisEdge May 18 '17 at 12:40

score 0 · Answer 3 · answered May 18 '17 at 12:16

0

You should be able to just get the max from the batch as well assuming that you batch increases with each start time:

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

answered May 18 '17 at 12:16

Avitus

15,640
6
43
53

This will not work. For press 2 it will return: PRESS_2, 2054, 2017-05-20. So the batch is not correct. – Anonymoose May 18 '17 at 12:17

Select row with group by and max in JPQL

3 Answers3