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".
Go to:
PREV : Identifying Rows with No Changes Over Time.
NEXT : Querying Rows That Existed at Any Point During a Specific Period.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.