How to find ultimate parent

This is my dataset

drop table if exists #dataset
create table #dataset(CIF VARCHAR(255), PARENT_CIF VARCHAR(255), UP_CIF VARCHAR(255))

INSERT INTO #dataset
SELECT 45218547, 54044082, 54044082 union all
SELECT 54044082, 64349595, 64349595 union all
SELECT 59741694, 54044082, 54044082 union all
SELECT 64349595, 59741694, 59741694 union all
SELECT 189, 345978, 345978 union all
SELECT 22002363, 189, 189 union all
SELECT 28459008, 22002363, 22002363 union all
SELECT 29135817, 28459008, 28459008 union all
SELECT 29154411, 29135817, 29135817

and my expected result is like this

CIF Parent_CIF UP_CIF
45218547 54044082 64349595
54044082 64349595 64349595
59741694 54044082 64349595
64349595 59741694 64349595
189 345978 345978
22002363 189 345978
28459008 22002363 345978
29135817 28459008 345978
29154411 29135817 345978

Thanks

You have a recursive cycle on 64349595 in your example data. It can't logically be the uppermost ancestor.

Yeah, is it possible to get till fist cycle and then get the UP_CIF for that like
45218547 --> 54044082 --> 64349595 --> 59741694 -->54044082 so his parecnt_cif is ultimate parent 64349595

54044082 --> 64349595 --> 59741694 -->54044082 so his parecnt_cif is ultimate parent 64349595

59741694 --> 54044082 --> so his parecnt_cif is ultimate parent 64349595

64349595 --> 59741694 --> 54044082 --> so his parecnt_cif is ultimate parent 64349595

In true recursion, no. The reference is circular, so any point on the circle leads back to itself. For recursion to work properly it needs an exit condition, this data won't provide that as-is. You have what's called a cyclic graph, where your nodes end up pointing to themselves in an infinite loop.

Unless your data has a fixed maximum recursion level (let's say 5), you could write a recursive common table expression (CTE) that limits the recursion. However, since there's a cycle involved, you can't form the necessary anchor element that has no ancestors of its own. The only way to solve that is for an item to be its own immediate parent, e.g. CIF=PARENT_CIF.

Here are two CTEs to demonstrate the issue. The first one looks for a topmost parent CIF that has no ancestors, and therefore no cycles, and will find 345978 from the sample data:

;WITH topmost(top_CIF) AS (SELECT PARENT_CIF FROM #dataset EXCEPT SELECT CIF FROM #dataset)
,CTE(CIF,parent,top_CIF,level) AS (
SELECT a.*,z.top_CIF,1 AS level FROM #dataset A INNER JOIN topmost Z ON a.PARENT_CIF=z.top_CIF
UNION ALL
SELECT c.CIF,c.parent_CIF,b.top_CIF,b.level+1 FROM CTE b 
INNER JOIN #dataset c ON c.PARENT_CIF=b.CIF)
SELECT * FROM CTE 
ORDER BY CTE.top_CIF, CTE.CIF 
OPTION (MAXRECURSION 7)

The second CTE includes the cyclic values, it will error out even though MAXRECURSION is set:

;WITH topmost(top_CIF) AS (SELECT PARENT_CIF FROM #dataset INTERSECT SELECT CIF FROM #dataset)
,CTE(CIF,parent,top_CIF,level) AS (
SELECT a.*,z.top_CIF,1 AS level FROM #dataset A INNER JOIN topmost Z ON a.PARENT_CIF=z.top_CIF
UNION ALL
SELECT c.CIF,c.parent_CIF,b.top_CIF,b.level+1 FROM CTE b 
INNER JOIN #dataset c ON c.PARENT_CIF=b.CIF)
SELECT * FROM CTE 
ORDER BY CTE.top_CIF, CTE.CIF 
OPTION (MAXRECURSION 7)

If you remove the 54044082, 64349595 pair from the table, the cycle is broken and there's no error. It does however return a different top CIF 54044082. Which underscores the issue: having a cycle makes the starting point/topmost ancestor impossible to answer.

yeah like i want to break the cycle when first time cyclic reference happen like

45218547 --> 54044082 --> 64349595 --> 59741694 -->54044082 so his parecnt_cif is ultimate parent 64349595