> I've tried the query below but it returns all records not just the
> top two for each employee.
>
> select j.Employee, j.Job, j.Salary, j.EffectiveDate from JobHist j
> where j.EffectiveDate in (select first 2 k.EffectiveDate from
> JobHist k
> where k.Employee = j.Employee order by 1 descending)

This is interesting since I thought your version would work, maybe a
bug? If anyone else thinks that this should work, I'll post a note to
firebird-devel.

Anyway, this slightly modified version seem to return correct results on
1.5RC6, if you can guarantee that there's at least 3 records for each
employee, since I'm not sure the proper behaviour of 'select first n
skip m' when m exceeds the total record count.
...
select j.employee, j.job, j.salary, j.effectivedate
from jobhist j
where ( j.effectivedate >
( select first 1 skip 2 k.effectivedate
from jobhist k
where k.employee = j.employee
order by 1 descending
)
)
order by j.employee, j.effectivedate
...

When tested on a table with 200.000 records, it was slow, as
expected....:) Maybe a storedproc would be better if you have large tables.

Salam,
sugi.