--- In firebird-support@yahoogroups.com, "Ian A. Newby" wrote:

> I have tried
>
> select p.*, (select first 1 j.jobtype from jobtype j where j.personid
> = p.personid and effectivedate <= current_timestamp order by
> effectivedate desc) from person p
>
> and
>
> select p.*, (select j.jobtype from jobtype j where j.personid =
> p.personid and effectivedate = (select max(effectivedate) from jobtype
> j1 where j1.personid = p.personid and j1.effectivedate <=
> current_timestamp)) from person p
>
> Are there any other, cleaner ways to acheive the same result?

I would have written this
select p.* j.jobtype
from person p
left join jobtype j
on p.personid = j.personid
and j.effectivedate <= current_timestamp
and not exists(select * from jobtype j2
where j2.effectivedate between j.effectivedate and current_timestamp
and ...)

The ... is to indicate that you need some way to avoid excluding the
record itself, I don't know your data and am short of time.

Good luck,
Set