Kashyap MNVL Kashyap MNVL - 3 months ago 10
SQL Question

How to get parent value and last replaced value in sql query

I have a scenario for which I need help with. I am kind of new to stack overflow so let me know if I make any mistakes in asking my question. Welcome your feedback.

I am working with a table in SQL where the values are as follows:

OldValue NewValue Date
------------------------------------
1 2 2016-08-01
2 3 2016-08-03
101 102 2016-08-06
102 103 2016-08-08
103 105 2016-08-14
201 202 2016-08-06
202 203 2016-08-08
203 205 2016-08-14
205 209 2016-08-18


I am trying to put forward a query that will get the oldest and the newest value by which the old one is replaced with. I am looking for an output that looks like this.

OldValue NewValue
--------------------------
1 3
101 105
201 209


The query I put forward for this is as follows:

select a.OldCPN,b.NewCPN from test..TestTable a inner join TestTable b
on a.NewCPN=b.OldCPN and a.date<=b.date


With the above query, I am getting all the values that are replaced at the intermediate levels also. But I would need only a row that has the oldest values and the newest one by which it is replaced with.

Any help with this is highly appreciated.
Thank you.

Answer

assuming the Value are in ascending order; newer date as larger value

using a recursive CTE

; with
cte as
(
    -- parent record
    select  parent = OldValue, OldValue, NewValue, Date
    from    sample_data d
    where   not exists
        (
            select  *
            from    sample_data x
            where   x.NewValue  = d.OldValue
        )

    union all

    -- child
    select  parent = c.parent, d.OldValue, d.NewValue, d.Date
    from    cte c
        inner join sample_data d    on  c.NewValue = d.OldValue
)
select  parent as OldValue, max(NewValue) as NewValue
from    cte 
group by parent