Selecting greatest or last date

[HRISUser]

I have two tables (Employee & Positions).

Table Employee

ID Employee Name
=================================
9999 John Doe
9998 Jane Doe
9997 Sally Doe


Table Positions
=================================
ID EmployeeID Position Start Date
Baker 9998 1/1/2004
Bakery Sup. 9998 10/1/2005
Bakery Manager 9998 1/1/2006
Bakery Sup. 9997 11/1/2005
Baker 9999 9/15/2005

I want to select ALL the Employees from the Employee table AND select only the NEWEST position or the position with the greatest date.

Any help?

 

[Golom]

Try

Select E.ID, E.EmployeeName, 
       MAX(P.[PositionStartDate]) As LatestDate

From Employee E INNER JOIN Positions P
     ON E.Employee = P.Employee

Group By E.ID, E.EmployeeName

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Golom]

D***! If only I could read ...

Select E.ID, E.EmployeeName, 
       MAX(P.[PositionStartDate]) As LatestDate

From Employee E INNER JOIN Positions P
     ON E.ID = P.ID

Group By E.ID, E.EmployeeName

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[PHV]

Something like this ?
SELECT E.ID, E.[Employee Name], P.ID, P.[Position Start Date]
FROM (Employee AS E
INNER JOIN Positions AS P ON E.ID = P.EmployeeID)
INNER JOIN (
SELECT EmployeeID, Max([Position Start Date]) AS LastDate
FROM Positions GROUP BY EmployeeID
) AS L ON P.EmployeeID = L.EmployeeID AND P.[Position Start Date] = L.LastDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886