Hi,

I'm in need of some help. I have a table that has a record of employees and their info (H). Every record has a date. Now, every employee will enter timesheets (T) so I need the most recent (H) for each (T).

Now I want to return H,T pairs using a hibernate query. H is mapped as a composite element of a set of an Employee record (E).

So initially, you would think of something like

select H,T from H inner join T where T.date >= H.date and H.date = (select max(date) from H where date <= T.date) group by T.E.id

but I wanted to get rid of the subquery, which turned into something like

select max(H.date), T.date, T.E.id, min(T), min(H) where T.date >= H.date group by T.E.id, T.date

which would work, except that H has a boolean column and min doesn't work on boolean values. In fact, none of the aggregate functions that would return an actual value of my boolean will work. What do I do? is there another way to rewrite the query without the subquery?