Incorrect order in the Table function (keep the "order" of the recursive CTE)

Question

A few minutes ago I asked here how to get parent records with recursive CTE. This works now, but I get the wrong order (backward ordered by PK identifier) ​​when I create a Value function that returns all parents. I cannot order directly because I need the logical order provided by CTE.

This gives the correct order (from the next parent to this parent, etc.):

declare @fiData int;
set @fiData=16177344;
WITH PreviousClaims(idData,fiData) 
AS(
    SELECT parent.idData,parent.fiData
    FROM tabData parent
    WHERE parent.idData = @fiData

    UNION ALL

    SELECT child.idData,child.fiData
    FROM tabData child
    INNER JOIN PreviousClaims parent ON parent.fiData = child.idData
)
select iddata from PreviousClaims

But the following function returns all records in reverse order (ordered by PK):

CREATE FUNCTION [dbo].[_previousClaimsByFiData] (
    @fiData INT
)

RETURNS @retPreviousClaims TABLE 
(
    idData int PRIMARY KEY NOT NULL
)
AS 
BEGIN
    DECLARE @idData int;

    WITH PreviousClaims(idData,fiData) 
    AS(
        SELECT parent.idData,parent.fiData
        FROM tabData parent
        WHERE parent.idData = @fiData

        UNION ALL

        SELECT child.idData,child.fiData
        FROM tabData child
        INNER JOIN PreviousClaims parent ON parent.fiData = child.idData
    )

    INSERT INTO @retPreviousClaims
        SELECT idData FROM PreviousClaims;
    RETURN;
END;

select * from dbo._previousClaimsByFiData(16177344);

---

UPDATE: , CTE ( "" ), , . , CTE - , . , CTE , , . TVF, id_ .

. TVF. ...

RETURNS @retPreviousClaims TABLE 
(
   idData int PRIMARY KEY NOT NULL
)

...

RETURNS @retPreviousClaims TABLE 
(
     idData int
)

.. "" ( , CTE).

UPDATE2: , "CTE-Order" , relationLevel CTE, (, , fe ssas). , Inline-TVF ( ) :

CREATE FUNCTION [dbo].[_previousClaimsByFiData] (
    @fiData INT
)

RETURNS TABLE AS
RETURN(
    WITH PreviousClaims 
    AS(
        SELECT 1 AS relationLevel, child.*
        FROM tabData child
        WHERE child.idData = @fiData

        UNION ALL

        SELECT relationLevel+1, child.*
        FROM tabData child
        INNER JOIN PreviousClaims parent ON parent.fiData = child.idData
    )

    SELECT TOP 100 PERCENT * FROM PreviousClaims order by relationLevel
)

:

select idData,fiData,relationLevel from dbo._previousClaimsByFiData(46600314);

.

Answer 1

ORDERING - ORDER BY . , ( , / , ).

- , , D MSDN WITH:

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM dbo.MyEmployees 
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.MyEmployees AS e
        INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID 
)

- EmployeeLevel CTE, .

Answer 2

, , CTE , . , (, - , tabData). , TVF , ORDER BY SELECT, , , :

select * from dbo._previousClaimsByFiData(16177344) order by idData

Answer 3

ORDER BY - , SELECT TVF.

"" .

, ORDER BY.

, ORDER BY SELECT:

 SELECT * FROM dbo._previousClaimsByFiData(16177344) 
 ORDER BY (whatever you want to order by)....

ORDER BY TVF:

INSERT INTO @retPreviousClaims
    SELECT idData FROM PreviousClaims
    ORDER BY idData DESC (or whatever it is you want to order by...)