Getting the most recent records from a history table.

Assume we have a History table with multiple records per Employee that looks like this :

It contains 3 fields an EmployeeID,StartDate and HistoryInfo

We are only interested in the most recent record per employee. So this would be records marked in yellow :

To select these 3 records, let’s first create a SQL statement that adds a columns with a rank per employee :

Add another calculated column to number the records belonging to the same Employee

Now place this in a WITH-Clause called BaseTable and select only rows where RankNumb is equal to RowNumk, like this :

Now we have the most recent record per employee.