Not strong in recursive queries, so I would be grateful if you would help with such a task: there is a table Chair (Chair) and its structure: ID | Name | IsActive | HistLink ID | Name | IsActive | HistLink ID | Name | IsActive | HistLink . When the name of the department changes, the following occurs at the old department: The IsActive field c 1 changes to 0, and then the department is added (added) with a new name and an ID of the old department in the HistLink field. For example:

 ID | Name | IsActive | HistLink 3 | Старое_название | 0(была 1) | null 5 | Новое_название | 1 | 3

And somehow you need to define the current (active) ID by the 1st request, knowing the ID of the old department (And of course the name can change> 1 time). Thanks in advance.

    1 answer 1

     with cte as (select Id, Name from chair, 0 as lvl where ID = 3 --Идентификатор первого значение в рекурсивном дереве union all select chair.id, chair.name, cte.lvl + 1 as lvl from chair inner join cte --рекурсивный джойн on chair.HistLink = cte.ID) select top 1 id, Name --выбираем единственную запись from cte order by lvl desc -- которая является самой последней в "дереве" переименований
    • Thank you, only here I am confused by the chair. History Link = cte. ID. I don’t have an old department (the field HistLink) refers to a new one, but on the contrary, the modified one refers to the old one. I tried to just change the chair. ID = cte.HistLink, but something swears on cte. - nvse
    • Did you try to fulfill the request? Meaning what - you get the first row of the tree: id = 3. In the second step, you add to it all her daughters HistLink = 3 (3 is the id from the first row - from the already formed cte table, therefore cte.id). In the third step, you join the second level daughters .... At the end, select the first record from the table, sorted in descending order of the level number, that is, choose the last record. - minamoto
    • Exactly, thanks. - nvse