Comparing rows in a sql table

Question

I have a table which tracks changes to an entity, and I'm trying to extract the changes. The structure is more or less this:

| RowNumber | Value | SourceID| TargetID |
|  1        | A     |   100   | 50       |
|  2        | B     |   100   | 100      |
|  3        | C     |   200   | 100      |

My select is

select t1.Value as Old, t2.Value as New from MyTable t1 
inner join MyTable t2 on t1.SourceID = t2.TargetID
where t1.value != t2.value

Which gives me :

|Old|New|
|A  | B |
|A  | C |
|B  | C |

The problem is, the data was changed from A->B, then from B->C. It never actually changed from A->C and I can't for the life of me find a way of doing this in one query, I realise that a cursor could achieve this going through the rows in order.

Is this possible in one query?

Answer 1

You can use the ROW_NUMBER window function to find the first next one.

Example:

declare @MyTable table (RowNumber int primary key identity(1,1), [Value] varchar(30), SourceID int, TargetID int);

insert into @MyTable ([Value], SourceID, TargetID) values
('A', 100, 50),
('B', 100, 100),
('C', 200, 100);

SELECT Old, New
FROM
(
    select 
     t1.[Value] as Old, 
     t2.[Value] as New,
     row_number() over (partition by t1.RowNumber order by t2.RowNumber) as RN
    from @MyTable t1 
    join @MyTable t2 
      on t2.TargetID = t1.SourceID AND t2.RowNumber > t1.RowNumber
) q
WHERE RN = 1;

Returns:

Old New
A   B
B   C