skip to Main Content

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


  1. 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
    
    Login or Signup to reply.
  2. 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;
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search