Lost on creating a query for this scenario

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 employee information as well as any changes been made to employee attributes. This way we can get a complete audit of changes been made. When OldID is NULL, that basically mean the latest data. Here is an example, I am using integer values for 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

To start with I can get the unique employees from table by

SELECT ID, Name FROM Employee WHERE OldId IS NULL

I am looking to get latest ID but its earliest name. So that result should be two rows

ID     Name  
13     John3  
121    Smith

I am not sure how can I get these results. Any help will be highly appreciated.

Well, +1 for being probably the first person today to store a date/time value in a date/time column. — Aaron Bertrand, Aug 22 '13 at 20:45
if i have used guid in the example it would have been a little tricky to see the links that's why i have mentioned it in posting "I am using integer values for identifier to make this example easier to understand." — crazy novice, Aug 22 '13 at 20:51
It looks like you want to do a recursive self join. See here: http://stackoverflow.com/questions/1757260/simplest-way-to-do-a-recursive-self-join-in-sql-server — Andrew, Aug 22 '13 at 20:55
Could you please show what desired results you want? Do you want the earliest *and* latest names for *each* employee? In separate rows, or the same row? Word problems aren't fun (and so far two people have crashed and burned answers as a result). Show us the expected output so we don't have to guess. — Aaron Bertrand, Aug 22 '13 at 21:11
it should return 2 rows. For ID 13, it should return Jojn3. For ID 121, it should return Smith as this did not had any earlier record. — crazy novice, Aug 22 '13 at 22:02
I think it will start by SELECT ID, Name from Employee where OldID is null but then somehow for name it need to get earliest name for each id. — crazy novice, Aug 22 '13 at 22:03
I have added a little more explanation and expected results in question. — crazy novice, Aug 22 '13 at 22:08

score 1 · Accepted Answer · answered Aug 22 '13 at 22:34

Here's 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 demo.

Lost on creating a query for this scenario

1 Answers1