Using CTE to determine a specific Hierarchical ID for Family Members

I’m trying to figure out how to attach an incrementing ID to my resultset while using CTE.

My table has data like so:

PersonId   ParentLinkId   Relation  Name
   1          NULL           F       John Doe
   2           1             S       Jane Doe
   3           1             C       Jack Doe
   4           1             C       Jill Doe

I want to add a column called RelationId. Basically the “F” person will always get “1”, The relation “S” will always get “2” and any subsequent “C” relation will get 3,4,5…etc

They are linked by the ParentLinkId so ParentLinkId = PersonId.

I tried to use CTE to recursively increment this value but I keep getting stuck on an infinite loop

I tried :

WITH FinalData( ParentId, ParentLinkId, Name, Relationship, RelationshipId) AS 
(
    SELECT  ParentId
           ,ParentLinkId
           ,Name
           ,Relationship
           ,1
    FROM FamTable
    WHERE ParentLinkId IS NULL
    UNION ALL
    SELECT FT.ParentId
          ,ParentLinkId
          ,Name
          ,Relationship
          ,RelationshipId + 1
    FROM FamTable FT
    INNER JOIN FinalData ON FT.ParentLinkId = FinalData.ParentId
)
SELECT * FROM 
FinalData

This is the result I keep on getting:

PersonId   ParentLinkId   Relation     Name     RelationshipId
   1           NULL          F       John Doe         1
   2            1            S       Jane Doe         2
   3            1            C       Jack Doe         2
   4            1            C       Jill Doe         2

It should be

PersonId   ParentLinkId   Relation     Name     RelationshipId
   1           NULL          F       John Doe         1
   2            1            S       Jane Doe         2
   3            1            C       Jack Doe         3
   4            1            C       Jill Doe         4

I think I’m getting close using CTE but any help or prod in the right direction would be greatly appreciated!

Answer

This sounds like a simple row_number():

select f.*,
       row_number() over (partition by coalesce(ParentLinkId, PersonId)
                          order by (case when relation = 'F' then 1
                                         when relation = 'S' then 2
                                         when relation = 'C' then 3
                                    end), PersonId
                         ) as relationId                                         
from famtable f;

Here is a db<>fiddle.