How to find parent row for same table

I have table something like this:

childId | parentId
   1    |  null
   2    |   1
   3    |  null
   4    |   2

Column childId is primary key of this table and parentId is foreign key to same this table and have reference to column (childId).

And I need to call a function and send parameter (childId) and function will find the most parent row of this child.

Example: If I pass childId = 4, the output result need to be 1.

Is there any solution for this problem?

EDIT:

I need something like hierarchy top level row. I have tried with recursive CTE but I couldn’t get done.

Answer

It looks like a recursive CTE (common-table expression) is a good fit for this type of query.

Sample data

DECLARE @T TABLE (childId int, parentId int);

INSERT INTO @T VALUES
(   1    ,   null),
(   2    ,    1  ),
(   3    ,   null),
(   4    ,    2  );

Query

Replace constant 4 with a parameter. I’m including AnchorChildID and AnchorParentID to make it easier to understand the result and what is going on. Run this query without the final filter WHERE ParentID IS NULL to see how it works.

WITH
CTE
AS
(
    SELECT
        childId AS AnchorChildID
        ,parentId AS AnchorParentID
        ,childId AS ChildID
        ,parentId AS ParentID
    FROM @T AS T
    WHERE childId = 4

    UNION ALL

    SELECT
        CTE.AnchorChildID
        ,CTE.AnchorParentID
        ,T.ChildID
        ,T.ParentID
    FROM
        CTE
        INNER JOIN @T AS T ON T.ChildID = CTE.ParentID
)
SELECT ChildID
FROM CTE
WHERE ParentID IS NULL
OPTION(MAXRECURSION 0)
;

Result

ChildID
1