Hi,

I have a similar situation, and use one query like this:

SELECT FIRST 1 Employee, Job, Salary, EffDate
FROM JobHist
WHERE Employee=XXXXXX
ORDER BY EffDate DESC;

I would appreciate other opinions.


With Regards,

José Lacerda
UtilSoft, Lda
Portugal

--- In firebird-support@yahoogroups.com, "gorepj" <peter@g...> wrote:
> I'm sure there is a simple solution but I just can't seem to find
it.
> I need to select the most recent record for a particular employee
> thus.
>
> JobHist
>
> Employee Job Salary EffDate
> 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
> 10000002 Manager 13,000 25-Oct-2002
>
> If I select Max(EffDate) and group by Employee I can't get the
> associated Job and Salary :(