Im not the best sql person, probably because I dont have must experiance with it yet but, anyway i need to get this query...

I have a table of employees and a seprate table of positions.
The positions table has the id, name of the position, and date started. The most current date is the current position

I need to select the employee along with their current position (just their current position) this is what I came up with.


select a.Actsid, a.Actssn, a.Actfname, a.Actmname, a.Actlname, p.posname, p.posdate;
from actives a left outer join position p ON a.ACTSID = p.POSSID;
where p.posdate = (select top 1 po.posdate from position po where possid = a.actsid order by posdate desc);
order by actlname

This subquery selects the most current date in the position table.
select top 1 po.posdate from position po where possid = a.actsid order by posdate desc

Am I doing this totally wrong. I keep getting an error and it wount run...

let me know if i need to explain more

Thanks in advance...
-Eric