You can do this either by (1) running a subquery if your RDBMS supports it, (2) using an "esists," or (3) use group by.
Here are the sketches of how to do that:
1.
Code:
select
entry e
left join
e.task t
where
t.regdate = (select
max(tx.regdate)
from
Task tx
where
tx.entryid = e.id
)
2.
Code:
select
entry e
left join
e.task t
where
not exist = (select
tx
from
Task tx
where
tx.regdate > t.regdate
)
3.
Code:
select
t.entry.name, max(t.regdate)
from
Task t
group by
t.entry.id
The (3) is probably the cleanest and most portable solutuion. If you'd like to retrieve entry itself, not just its name, you'll need to list all fields of the entry in the group by list (this is a limitation, at least in the version that I have). Also listing all entries will not work on Oracle if one of the fields is a BLOB.
I hope this helps.
Login
FAQ
Search
