Lost when creating a request for this script

Question

Here is a simplified version of the table structure.

Employee ( ID GUID NOT NULL OldID GUID NULL Name VARCHAR(50) NOT NULL CreationDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) 

It contains information about employees, as well as any changes made to employee attributes. In this way, we can get a complete audit of the changes. When OldID is NULL, it basically means the latest data. Here is an example, I use integer values ​​for an identifier to make this example easier to understand.

 ID OldId Name CreationDate 13 NULL John 15-July-2013 12 13 John1 14-July-2013 11 12 John2 13-July-2013 10 11 John3 12-July-2013 121 NULL Smith 15-July-2013 

For starters, I can get unique employees from the table.

 SELECT ID, Name FROM Employee WHERE OldId IS NULL 

I am looking to get the last identifier, but its earliest name. Thus, the result should consist of two lines

 ID Name 13 John3 121 Smith 

I am not sure how I can get these results. Any help would be greatly appreciated.

Answer 1

Here is one approach that works for your data:

 with groups as ( select groupID = ID, * from Employee where OldID is null union all select g.groupID, e.* from groups g inner join Employee e on g.ID = e.OldID ) , ranks as ( select * , firstRank = row_number () over (partition by groupID order by creationDate) from groups ) select ID = groupID , Name from ranks where firstRank = 1 

SQL Fiddle with a demo .