none
SuperSedes Chain

    Question

  • Hello All,

     

    I have 2 Columns namely ID, Supersedes... as supersedes name indicates in simple words replacement ...i have record like for example... in this case 1 has been replaced by 2....

     

    ID                  Supersedes

    1                    Null

    2                    1

    3                    2

    4                    3

    5                    Null

     

    So i want output as  1 - 2 - 3 if the ID is 1

    2 - 3 If the ID is 2

     

    And secondly the supersedes can grow upto 10 level down

     

    So i need to write a function to get that pattern...

     

    Any help is really appreciated ....Thanks in Advance

     

    Regards,

     

    Modiferrari.

    Tuesday, January 04, 2011 5:08 PM

Answers

  • Try

    create table WordsInfo (ID int, Supersedes int)
    
    insert into WordsInfo
    select
    1,          Null
    union all select
    2,          1
    union all select
    3,          2
    union all select
    4,          3
    union all select
    5,          Null
    go
    
    
    alter function ufnSuperceedes(@ID int) returns table
    as
    return with cte as (select ID, Supersedes, cast(ID as varchar(max)) as Lvl, 0 as [Level]
     from WordsInfo where ID = @ID
    UNION ALL
    
    select T.ID, T.Supersedes, Lvl + '-' + cast(T.ID as varchar(max)) as Lvl, 1 + [Level] as [Level]
    from cte inner join WordsInfo T on cte.ID = T.Supersedes )
    
    select top 1 Case when Lvl = CAST(@ID AS varchar(max)) then '' else Lvl end as Output from cte order by [Level] DESC 
    go
    
    select W.*, F.Output from WordsInfo W CROSS APPLY ufnSuperceedes(ID) F
    

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by modiferrari Tuesday, January 04, 2011 7:31 PM
    Tuesday, January 04, 2011 7:23 PM

All replies

  • Not sure if this is what you're looking for:

    declare @t table(ID int, Supersedes int)
    
    insert into @t 
    select
    1,          Null
    union all select
    2,          1
    union all select
    3,          2
    union all select
    4,          3
    union all select
    5,          Null
    
    declare @Id int = 2
    
    ;with cte as (select ID, Supersedes, cast(ID as varchar(max)) as Lvl, 0 as [Level]
     from @t where ID = @ID
    UNION ALL
    
    select T.ID, T.Supersedes, Lvl + '-' + cast(T.ID as varchar(max)) as Lvl, 1 + [Level] as [Level]
    from cte inner join @t T on cte.ID = T.Supersedes )
    
    select top 1 * from cte order by [Level] DESC
    

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, January 04, 2011 5:33 PM
  • Create Function dbo.FindSupersedes(@ID int) 
    Returns varchar(max) As
    Begin
    Declare @Result varchar(max);
    With cte As
    (Select ID, Supersedes, Cast(ID As varchar(max)) As Result
    From YourTable
    Union All
    Select c.ID, y.Supersedes, c.Result + ' - ' + Cast(y.ID As varchar(max))
    From cte c
    Inner Join YourTable y On c.Supersedes = y.ID)
    Select @Result = Max(Result)
    From cte
    Where ID = @ID;
    Return @Result;
    End
    
    Tom
    Tuesday, January 04, 2011 5:35 PM
  • Hi Naom,

     

    Actually what i want is

     

    If ID = 1is pass then My output should be 1-2-3.. according to our previous example

    If ID =2 is pass then my output should be 2-3 ...

    the reason why i need is

    1 is been replaced by 2 and then again replaced by 3... so i need tracking of which is being replaced by which...

     

    I hope you got my point... Thanks for replying i ran your query but the output what i want i am not able to get ...

     

    Regards,

     

    Vaibhav

    Tuesday, January 04, 2011 6:49 PM
  • Hi Tom,

     

    Thanks for replying me... the query is working but in reverse way....

     

    as per our example

     

    ID                  Supersedes

    1                    Null

    2                    1

    3                    2

    4                    3

    5                    Null

     

     

    If i Pass ID = 4 i am getting output as 3-2-1... but it should be reverse

     

    If i Pass ID =1 then i should get 1-2-3

    If i pass ID =2 then i should get 2-3

    If I pass ID =3 then i should get 3-4

    If i Pass ID =4 then I should get nothing..

     

    Please help me out.... Thanks In Advance..

     

    Regards,

     

    Modiferrari

    Tuesday, January 04, 2011 6:57 PM
  • Hi Naom,

     

    I am sorry ... u r correct 3 is also replaced by 4 i need 1-2-3-4... it can be more than 1 levels... currently what i see is there can happen 10 replacement ... so its better to have function so if i pass a ID i should get their replacement records...

     

    Thanks you so much in advance....

     

     

    Tuesday, January 04, 2011 7:04 PM
  • Hi Naom,

     

    Yeah i have tried it ... your query gives me this output

     

    ID    Supersedes      Lvl        Level
    1      NULL                1            0
    2      1                     1-2         1
    3      2                     1-2-3      2
    4      3                     1-2-3-4   3

     

    I don't want in this fashion ...

     

    ID    Supersedes     O/P       
    1      NULL                1-2-3-4
    2      1                     2-3-4        
    3      2                     3-4     
    4      3                     Nothing

     

    Thanks

    Tuesday, January 04, 2011 7:11 PM
  • hi Naom,

     

    i just removed the top1 and desc from the last query to see what excatly the output is coming but still its not coming what i want ...what i send u thats the output is coming ...

     

    Thanks

    Tuesday, January 04, 2011 7:16 PM
  • Try

    create table WordsInfo (ID int, Supersedes int)
    
    insert into WordsInfo
    select
    1,          Null
    union all select
    2,          1
    union all select
    3,          2
    union all select
    4,          3
    union all select
    5,          Null
    go
    
    
    alter function ufnSuperceedes(@ID int) returns table
    as
    return with cte as (select ID, Supersedes, cast(ID as varchar(max)) as Lvl, 0 as [Level]
     from WordsInfo where ID = @ID
    UNION ALL
    
    select T.ID, T.Supersedes, Lvl + '-' + cast(T.ID as varchar(max)) as Lvl, 1 + [Level] as [Level]
    from cte inner join WordsInfo T on cte.ID = T.Supersedes )
    
    select top 1 Case when Lvl = CAST(@ID AS varchar(max)) then '' else Lvl end as Output from cte order by [Level] DESC 
    go
    
    select W.*, F.Output from WordsInfo W CROSS APPLY ufnSuperceedes(ID) F
    

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by modiferrari Tuesday, January 04, 2011 7:31 PM
    Tuesday, January 04, 2011 7:23 PM
  • Hi Naom,

     

    Perfect... Thanks so much ... just little bit modification put dbo in front of function and table and instead of alter create function...

     

    Thanks what i wanted thats what this query does... Thank you again...

     

    Regards,

     

    ModiFerrari...

    Tuesday, January 04, 2011 7:31 PM