sql server - How to get parent value and last replaced value in SQL query -

i have scenario need with. kind of new stack overflow let me know if make mistakes in asking question. welcome feedback.

i working table in sql server values 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 trying put forward query oldest , newest value old 1 replaced with. looking output looks this.

   oldvalue     newvalue   --------------------------      1             3                   101           105         201           209      

the query put forward follows:

select      a.oldcpn, b.newcpn       test..testtable  inner join      testtable b on a.newcpn = b.oldcpn , a.date <= b.date 

with above query, getting values replaced @ intermediate levels also. need row has oldest values , newest 1 replaced with.

any highly appreciated.

thank you.

assuming value in ascending order; newer date larger value

using recursive cte

; cte (     -- parent record     select  parent = oldvalue, oldvalue, newvalue, date        sample_data d       not exists         (             select  *                sample_data x               x.newvalue  = d.oldvalue         )      union      -- child     select  parent = c.parent, d.oldvalue, d.newvalue, d.date        cte c         inner join sample_data d    on  c.newvalue = d.oldvalue ) select  parent oldvalue, max(newvalue) newvalue    cte  group parent 

edit : change last part of query below handle non-ascending value

select  * (     select  parent oldvalue, newvalue, date, rn = row_number() on (partition parent order date desc)        cte  ) c   c.rn = 1