Recursive or Join

Question

Table has old insurance policies and their new policy numbers.Like this:

OldPol  |   NewPol
------  |  ------
AA1     |   AA2
AA2     |   AA3
AA3     |   AA5
AA5     |   AA9

I need to come up with a query that should show latest policy number for all old policies:

OldPol    NewPol
------    -----
AA1        AA9
AA2        AA9
...        AA9
AA5        AA9

Tried recursive query - it failed due to allowed max speed or something, self join worked up to 2 levels only. Some policies have only 2 levels, but some has up to 9 levels.

You need a more extensive test case. That is just one chain. — paparazzo, Mar 24 '18 at 13:26
May we see what you tried? Tip: It's helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. `sql-server-2014`. Differences in syntax and features often affect the answers. Note that `tsql` narrows the choices, but does not specify the database. — HABO, Mar 24 '18 at 13:28
Three possibly useful related questions: [A](https://dba.stackexchange.com/questions/46238/linked-list-in-sql-and-trees) [B](https://stackoverflow.com/questions/1246725/iterate-through-linked-list-in-one-sql-query) [C](https://stackoverflow.com/questions/30963130/get-last-child-from-sql-hierarchyid) — lsowen, Mar 24 '18 at 13:30
@HABO ;with CTE as (select oldpol, newpol from DimPol Union ALL select oldpol, newpol from DimPol p join cte on p.oldpol = cte.newpol) select * from cte — Batman, Mar 24 '18 at 13:44
@Squirrel I used `OPTION (MAXRECURSION 32767)` but not 0. I will use try it and get back — Batman, Mar 24 '18 at 13:53
If you would have taken the effort to post a better test I would have answered this. — paparazzo, Mar 24 '18 at 14:16
@paparazzo I will once I get to my desk, writing from a hotel, don't have my work laptop with me right now, sorry about that. Thanks for all helping me here. — Batman, Mar 24 '18 at 14:29
Are you sure that you don't have any loops in your data? [This](https://stackoverflow.com/questions/15080922/infinite-loop-cte-with-option-maxrecursion-0/15081353#15081353) answer demonstrates one way of detecting loops and terminating. — HABO, Mar 24 '18 at 14:29

score 0 · Answer 1 · answered Mar 24 '18 at 16:17

textbook recursive cte

declare @T table (old varchar(10), new varchar(10));
insert into @T values 
       ('AA1', 'AA2')
     , ('AA2', 'AA3')
     , ('AA3', 'AA5')
     , ('AA5', 'AA9')
     , ('AB2', 'AB3')
     , ('AB3', 'AB5')
     , ('AC3', 'AC5');
with cte as  
( select t.old,   t.new, cnt = 1  
  from @T t 
  union all 
  select cte.old, t.new, cte.cnt + 1
  from cte 
  join @t t 
    on t.old = cte.new
)
, cte2 as 
( select t.old, t.new, t.cnt 
       , ROW_NUMBER() over (partition by t.old order by t.cnt desc) rn 
    from cte t 
)
select *
from cte2 t 
where t.rn = 1
order by t.old, t.new;

Recursive or Join

1 Answers1