How to rewrite a query without a subquery-CodePudding

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

CodePudding user response：

Maybe something like this?

select 
    qt.id as id, 
    ev.id as event_id, 
    coalesce( res.allocation_date, max.allocation_date ) as date 
from 
    quota qt 
    left join 
    event ev on ev.quota_fk = qt.id
    left join 
    (
        select quota_fk, max(allocation_date) as allocation_date
        from result
        group by quota_fk
    ) res on res.quota_fk = qt
    cross join
    (
        select max(allocation_date) as allocation_date 
        from result
    ) max

CodePudding user response：

You only want those rows that have the greatest allocation date per quota:

with data as (
    select 
        qt.id as id, ev.id as event_id, res.allocation_date as date,
        dense_rank() over (partition by qt.id order by res.allocation_date desc) rn 
    from quota qt
        left join result res on qt.id = res.quota_fk 
        left join event ev   on qt.id = ev.quota_fk
)
select * from data where rn = 1;