>>See if this works:
>>
>>select fieldlist from mytable where id in (select id, max(effectdate) from mytable group by id)
>
>Thanks for the quick reply Mark. Unfortunately since we're using the IN clause the sub-select would have to be on one value only :(

Well that was dumb of me. How about creating a view in your Oracle DB:

create or replace view someview as select id, max(effectdate) as LastDate from mytable group by id

Then retrieving the records would be quite simple using the subquery.