Peter, if each date is unique, you could do

select j.Employee, j.Job, j.Salary, j.EffectiveDate from JobHist j
where not exists(
select * from JobHist k where
k.Employee = j.Employee and
k.EffectiveDate > j.EffectiveDate and exists(
select * from JobHist m where
m.Employee = k.Employee and
m.EffectiveDate > k.EffectiveDate))

Translated to English, this statement says that for the same employee,
there should not exist any later record that is not the last record.

Set

--- In firebird-support@yahoogroups.com, "gorepj" <peter@g...> wrote:
> I want to return the two most recent rows for each employee's
> records from a job history table.
>
> JobHist table
> Employee Job Salary EffectiveDate
> 10000001 Supervisor 12,000 25-Oct-2002
> 10000001 Manager 12,500 25-Oct-2001
> 10000001 Supervisor 11,000 25-Oct-2000
> 10000002 Manager 13,000 25-Oct-2002
> 10000002 Supervisor 12,000 25-Jun-2002
> 10000002 Assistant 10,000 20-Jan-2002
> ...
>
> Required Result
>
> 10000001 Supervisor 12,000 25-Oct-2002
> 10000001 Manager 12,500 25-Oct-2001
> 10000002 Manager 13,000 25-Oct-2002
> 10000002 Supervisor 12,000 25-Jun-2002
> ...
>
> 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)
>
> Regards
> Peter Gore