"congling" <congl***@hotmail.com> wrote in message
news:OK9CPzwAGHA.1312@TK2MSFTNGP09.phx.gbl...
> Hi all,
>    I tested a new feature of SQL2005 (named cte for recursive selection)
> and found the order of the result was weird.
>    According to the description of help:
>        1. Split the CTE expression into anchor and recursive members.
>        2. Run the anchor member(s) creating the first invocation or base
> result set (T0).
>        3. Run the recursive member(s) with Ti as an input and Ti+1 as an
> output.
>        4. Repeat step 3 until an empty set is returned.
>        5. Return the result set. This is a UNION ALL of T0 to Tn.
>
>    But the result was just like the stack-calls.
>    Is there something wrong on the description of the help?
>

"David Browne" <davidbaxterbrowne no potted m***@hotmail.com> дÈëÏûÏ¢ÐÂÎÅ:%23CcF$FxAGHA.2***@TK2MSFTNGP14.phx.gbl...
>
> "congling" <congl***@hotmail.com> wrote in message
> news:OK9CPzwAGHA.1312@TK2MSFTNGP09.phx.gbl...
>> Hi all,
>>    I tested a new feature of SQL2005 (named cte for recursive selection)
>> and found the order of the result was weird.
>>    According to the description of help:
>>        1. Split the CTE expression into anchor and recursive members.
>>        2. Run the anchor member(s) creating the first invocation or base
>> result set (T0).
>>        3. Run the recursive member(s) with Ti as an input and Ti+1 as an
>> output.
>>        4. Repeat step 3 until an empty set is returned.
>>        5. Return the result set. This is a UNION ALL of T0 to Tn.
>>
>>    But the result was just like the stack-calls.
>>    Is there something wrong on the description of the help?
>>
>
> The order of a result is always undefined unless the query contains an
> ORDER BY clause.  The BOL has this description under the heading of
> "Pseudocode and Semantics", and it's is a logical description of the
> result set, not a guarantee about how it is processed or the order in
> which the rows are returned.
>
> David
>

> Hi all,
>     I tested a new feature of SQL2005 (named cte for recursive selection)
> and found the order of the result was weird.
>     According to the description of help:
>         1. Split the CTE expression into anchor and recursive members.
>         2. Run the anchor member(s) creating the first invocation or base
> result set (T0).
>         3. Run the recursive member(s) with Ti as an input and Ti+1 as an
> output.
>         4. Repeat step 3 until an empty set is returned.
>         5. Return the result set. This is a UNION ALL of T0 to Tn.
>
>     But the result was just like the stack-calls.
>     Is there something wrong on the description of the help?
>
>
> table creation script:
>     CREATE TABLE tbUser(
>          userId int NOT NULL,
>         mgrId int NULL,
>      CONSTRAINT [PK_tbUser] PRIMARY KEY CLUSTERED
>      (
>          [userId] ASC
>       )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
>     ) ON [PRIMARY]
>
> table values:
>     insert tbUser values(1,null)
>     insert tbUser values(2,1)
>     insert tbUser values(3,2)
>     insert tbUser values(4,2)
>
> CTE scripts
>     WITH reps_cte (userId, mgrId, recursion_level)
>     AS
>     (
>          SELECT userId, mgrId, 0 FROM tbUser
>          UNION ALL
>          SELECT reps_cte.userId, tbUser.mgrId, recursion_level+1 --get the
> higher level of the mgr
>               FROM  reps_cte inner join tbUser        -- Join with Employee
>               on reps_cte.mgrId= tbUser.userId    -- This employee's manager
>               where recursion_level<=20        -- up to 20 levels of mgmt
>     )
>
> Result:
>     1 NULL 0
>     2 1 0
>     3 2 0
>     4 2 0
>     4 1 1
>     4 NULL 2
>     3 1 1
>     3 NULL 2
>     2 NULL 1
>
> Expecting result:
>     1 NULL 0        - T0 result
>     2 1 0           - T0 result
>     3 2 0           - T0 result
>     4 2 0           - T0 result
>     2 NULL 1        - T1 result
>     3 1 1           - T1 result
>     4 1 1           - T1 result
>     3 NULL 2        - T2 result
>     4 NULL 2        - T2 result
>
>
>
> Regards,
> congling