Forum Discussion
Subset in a larger set
Hello
I have a large table with positions (personnel). Some positions may be occupied by more than one person during the year; consequently, position numbers repeat if I also get the name of the person and the date.
| Position | Name | LastName | EndDate |
| 1234 | John | Sullivan | 31/12/2020 |
| 8546 | Susan | Kent | 03/15/2020 |
| 8546 | Juan | Valdez | 09/30/2020 |
| 3454 | Samir | Bhutteer | 31/10/2020 |
How can I run a qry to get only the second record for 8546, that is the most recent occupancy?
I guess I need to nest the SELECT statements, but I don't know how.
Thank you
Leonel
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.MaxofEndDateYou 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.
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.MaxofEndDateYou 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.
- Thank you!
- Continued success with your project.
