--- In firebird-support@yahoogroups.com, Svein Erling Tysvær 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

OK, back again and finishing off what I started with a few more comments:

I forgot one very important detail: To check for the correct person in
the EXISTS:

and p.personid = j2.personid

It is better to use JOIN than LEFT JOIN if your original queries
didn't return rows that where NULL due to no jobtype existing.

not exists(select * from jobtype j2
where j2.effectivedate > j.effectivedate
and j2.effectivedate <= current_timestamp)

is sufficient if no person have several records with the same
effectivedate.

So this is the SQL I suggest if my above assumptions are correct (I
also assume that effectivedate is never NULL):

select p.* j.jobtype
from person p
join jobtype j on p.personid = j.personid
where j.effectivedate <= current_timestamp
and not exists(select * from jobtype j1
where p.personid = j1.personid
and j1.effectivedate > j.effectivedate
and j1.effectivedate <= current_timestamp)

If you don't like my suggestion, I'd say the first of your suggestions
is far better than the second. Though I may be biased since Firebird
used to do a horrible job in optimizing IN <subselect> and there were
lots of mails on this list with people disappointed by the performance
(later Arno changed the optimizer to translate IN <subselect> to
EXISTS wherever possible).

HTH,
Set