Subset in a larger set

Leonel GUZMAN 

We would have to have the actual table to provide a more specific answer, but based on the data provided, it would be something like this:

SELECT tblPositions.Position, tblPositions.FirstName, tblPositions.LastName, tblPositions.EndDate
FROM tblPositions INNER JOIN (SELECT tblPositions.Position, Max(tblPositions.EndDate) AS MaxOfEndDate
FROM tblPositions
GROUP BY tblPositions.Position) AS LastEndDate ON tblPositions.Position = LastEndDate.Position and tblPositions.EndDate = LastEndDate.MaxofEndDate

You are right that a subquery is needed to get the most recent EndDate for each Position. This requires the MAX operator on the EndDate field, grouped by Position. The subquery is inserted into the main query in parentheses and given an alias, LastEndDate.

The Join on both Position and Enddate between the outer query and the subquery limits the results to only the most recent EndDate for each position, along with other values, which are First and Last Names in this case.