That worked a treat, thanks
Regards
Peter Gore

-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: 18 June 2003 05:51
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Simple Query?


At 04:24 AM 18/06/2003 +0000, you 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 :(

select j.Employee, j.Job, j.Salary, j.EffDate from jobhist j
where j.EffDate = (select max(k.Effdate) from jobhist k
where k.Employee = j.Employee)

heLen




Yahoo! Groups Sponsor

<http://rd.yahoo.com/M=251812.3170658.4537139.1261774/D=egroupweb/S=1705
115386:HM/A=1564415/R=0/SIG=11t6t7kdo/*http://www.netflix.com/Default?mq
so=60164784&partid=3170658>

<http://us.adserver.yahoo.com/l?M=251812.3170658.4537139.1261774/D=egrou
pmail/S=:HM/A=1564415/rand=720155143>

To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .




[Non-text portions of this message have been removed]