>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
Hi Eric,
The query below gives you the most current dates for positions:
Select EmpID, Max(DateStarted) as MaxOfDateStarted From Positions Group By EmpID;
The next query produces the most current positions:
Select * From Positions A Where DateStarted in (Select Max(DateStarted) From Positions B Group By EmpID Having B.EmpID=A.EmpID);
HTH, Igor
Igor Gelin
Database Developer