Wrong order in Table valued Function(keep "order" of a recursive CTE)

Question

Hello, a few minutes ago i asked here how to get parent records with a recursive CTE. This works now, but I get the wrong order(backwards, ordered by the PK idData) when i create a Table valued Function which returns all parents. I cannot order directly because i need the logical order provided by the CTE.

This gives the correct order(from next parent to that parent and so on):

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 backwards 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: Since everybody beliefs that the CTE is not ordering(Any "ordering" will be totally arbitrary and coincidental), i'm wondering why the opposite seems to be true. I have queried a child claim with many parents and the order in the CTE is exactly the logical order when i go from child to parent and so on. This would mean that the CTE is iterating from record to record like a cursor and the following select returns it in exact this order. But when i call the TVF i got the order of the primary key idData instead.

The solution was simple. I only needed to remove the parent key of the return-Table of the TVF. So change...

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

to...

RETURNS @retPreviousClaims TABLE 
(
     idData int
)

.. and it keeps the right "order" (same order they were inserted into the CTE's temporary result set).

UPDATE2: Because Damien mentioned that the "CTE-Order" could change in certain circumstances, i will add a new column relationLevel to the CTE which describes the level of relationship of the parent records (what is by the way quite useful in general f.e. for a ssas cube). So the final Inline-TVF(which returns all columns) is now:

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
)

This is an exemplary relationship:

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

Thank you.

Answer 1

I think the impression that the CTE is creating an ordering is wrong. It's a coincidence that the rows are coming out in order (possibly due to how they were originally inserted into tabData). Regardless, the TVF is returning a table so you have to explicitly add an ORDER BY to the SELECT you're using to call it if you want to guarantee ordering:

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

Answer 2

There is no ORDER BY anywhere in sight - neither in the table-valued function, nor in the SELECT from that TVF.

Any "ordering" will be totally arbitrary and coincidental.

If you want a specific order, you need to specify an ORDER BY.

So why can't you just add an ORDER BY to your SELECT:

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

or put your ORDER BY into the TVF:

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

Answer 3

The correct way to do your ORDERing is to add an ORDER BY clause to your outermost select. Anything else is relying on implementation details that may change at any time (including if the size of your database/tables goes up, which may allow more parallel processing to occur).

If you need something convenient to allow the ordering to take place, look at Example D in the examples from the MSDN page on 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 
)

Add something similay to the EmployeeLevel column to your CTE, and everything should work.