I have this query:
select
qt.id as id,
ev.id as event_id,
res.allocation_date as date
from quota qt
left join result res on qt.id=res.quota_fk
and (res.allocation_date=(select max(res2.allocation_date) from result res2 where res2.quota_fk=qt.id))
left join event ev on qt.id=ev.quota_fk
this query correctly prints id
and event_id
, but does not print a max date
(prints null).
Is it possible to rewrite this query in a way that it would somehow
reference max(res2.allocation_date) in an outer select part of the query without rewriting the query itself by adding a subquery in the outer select?
What I mean is that I want to select max(res2.allocation_date)
, but I don’t want to rewrite my query in the following ways.
(adds a subquery in select clause, [Hibernate 5.x does not allow subqueries in select])
select
qt.id as id,
ev.id as event_id,
(select max(res3.allocation_date) from result res3 where res3.quota_fk=qt.id) as date
from quota qt
left join result res on qt.id=res.quota_fk
and (res.allocation_date=(select max(res2.allocation_date) from result res2 where res2.quota_fk=qt.id))
left join event ev on qt.id=ev.quota_fk
And
(adds where clause at the end of the query [seems incorrect. Inefficient?])
select
qt.id as id,
ev.id as event_id,
res.allocation_date as date
from quota qt
left join result res on qt.id=res.quota_fk
and (res.allocation_date=(select max(res2.allocation_date) from result res2 where
res2.quota_fk=qt.id))
left join event ev on qt.id=ev.quota_fk
where (( res.allocation_date = (select max(allocation_date) from result where quota_fk = qt.id
2
Answers
Maybe something like this?
You only want those rows that have the greatest allocation date per quota: