Recursive CTE to Loop

Transact-SQL
1

I have table that stored name changes. I want to get the latest name as additional column. I think it might be done using CTE but I am not able to to get it working

OldName NewName YearofChange
Jon John 2021
John Jim 2022
Jim James 2023
James Jerry 2024
OldName NewName YearofChange MostCurrentName
Jon John 2021 Jerry
John Jim 2022 Jerry
Jim James 2023 Jerry
James Jerry 2024 Jerry
2

Assuming you'll also have some kind of UserID column:

SELECT  unc.UserID
    ,   unc.OldName
    ,   unc.NewName
    ,   unc.YearOfChange
    ,   mcn.MostCurrentName
FROM    UserNameChanges unc
CROSS APPLY
        (   SELECT   TOP (1) 
                     NewName AS MostCurrentName
             FROM    UserNameChanges m
             WHERE   m.UserID = unc.UserID
             ORDER BY m.YearOfChange DESC
        ) mcn;
3

unfortunately , I don't have userid, please assume that names are unique

4

How do you know that these 4 rows are associated? There has to be something that ties them together into a group. What if you had additional rows for a different individual - how would you know which row contains the latest version?

SELECT unc.OldName
     , unc.NewName
     , unc.YearOfChange
     , MostCurrentName = LAST_VALUE(unc.NewName) OVER(ORDER BY unc.YearOfChange ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  FROM dbo.UserNameChanges      unc
 ORDER BY
       unc.YearOfChange;

If you do have a way of 'grouping' the rows to identify each individual - that can be included in the PARTITION BY for the windows function.

1 Like
5 

DROP TABLE IF EXISTS #data;
CREATE TABLE #data ( OldName varchar(100) NOT NULL, NewName varchar(100) NOT NULL, YearOfChange smallint NOT NULL );
INSERT INTO #data VALUES
    ('Jon', 'John', 2021),
    ('John', 'Jim', 2022),
    ('Jim', 'James', 2023),
    ('James', 'Jerry', 2024)

;WITH cte_last_names_only AS (
    SELECT NewName
    FROM #data d1
    WHERE NOT EXISTS(SELECT * FROM #data d2 WHERE d2.OldName = d1.NewName)
),
cte_all_names AS (
    SELECT d.*, d.NewName AS MostCurrentName
    FROM cte_last_names_only clno
    INNER JOIN #data d ON d.NewName = clno.NewName
    UNION ALL
    SELECT d.*, can.MostCurrentName
    FROM #data d
    INNER JOIN cte_all_names can ON can.OldName = d.NewName
)
SELECT *
FROM cte_all_names
6

Although you have a couple of solutions (only one of which that follows a hierarchical path) I can see this leading to some serious issues for performance.

With that, I'll ask, what is the bigger picture for this? What are you trying to solve outside this one tiny requirement?

And what are the other columns that are available in this table?

7

hi

hope this helps

Another way of doing this

SELECT 
    *
	, (select top 1 newname from #data order by YearOfChange desc) 
FROM 
   #data

image

8

harishgg1: That code assumes there's ever only a single chain of names. I don't think that would be true.

9

thanks Scott for pointing it out

:+1: :+1: