How to Query First and Last Versions of Each Row in SQL

Querying the First and Last Versions of Each Row

Write a SQL query to retrieve the first and last versions of each row in a temporal table.

Solution:

-- Retrieve the first and last versions of each row.
WITH RowVersions AS (
    SELECT EmployeeID, Name, Position, SysStartTime, SysEndTime,
           ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY SysStartTime) AS RowNumAsc,
           ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY SysStartTime DESC) AS RowNumDesc
    FROM Employees
    FOR SYSTEM_TIME ALL
)
SELECT EmployeeID, Name, Position, SysStartTime, SysEndTime
FROM RowVersions
WHERE RowNumAsc = 1 OR RowNumDesc = 1
ORDER BY EmployeeID, SysStartTime;

Explanation:

• Purpose of the Query :
• The goal is to demonstrate how to retrieve the first and last versions of each row in a temporal table.
• Key Components :
• ROW_NUMBER: Assigns a unique number to each version of a row based on SysStartTime.
• Filters rows where RowNumAsc = 1 (first version) or RowNumDesc = 1 (last version).
• Why Query First and Last Versions?:
• Retrieving the first and last versions provides insights into the initial state and final state of each record.
• It supports auditing and historical analysis.
• Real-World Application :
• In HR systems, querying first and last versions tracks employee role changes from start to finish.

Additional Notes:

• Use this query for detailed record-level auditing.
• Order results by EmployeeID and SysStartTime for clarity.
• Important Considerations:
• Ensure that the query handles large datasets efficiently.

For more Practice: Solve these Related Problems:

• Write a SQL query to retrieve the first and last versions of each row in a temporal table named "Orders".
• Write a SQL query to fetch the initial and final states of rows in a temporal table named "Customers".
• Write a SQL query to extract the first and last versions of each record in a temporal table named "Products".
• Write a SQL query to display the starting and ending states of rows in a temporal table named "Transactions".

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Identifying Rows with No Changes Over Time.
Next SQL Exercise: Querying Rows That Existed at Any Point During a Specific Period.