Benjamin Benjamin - 8 days ago 5
SQL Question

Order a table according to a parent

I have used a recursive CTE to obtain the table below.

CREATE TABLE PositionTree(
OID int,
Position int,
Parent int,
Name varchar(50)
)

INSERT INTO PositionTree
(OID, Position, Parent, Name)
VALUES
(23, 21, 2, 'Supervisor'),
(34, 18, 18, 'Supervisor+3'),
(37, 1, 18, 'Supervisor+2'),
(89, 25, 21, 'Target Employee'),
(1129, 2, 1, 'Supervisor+1')


I would like to generate an index such that I can arrange the table to start with
'Target Employee'
and ascend up through
'Supervisor+3'
. You may assume that I know in advance the OID of the target employee (this will eventually go into a stored procedure, with the target OID as a parameter)

The resulting table should look similar to this:

OID Position Parent Name SortOrder
89 25 21 'Target Employee' 0
23 21 2 'Supervisor' 1
1129 2 1 'Supervisor+1' 2
37 1 18 'Supervisor+2' 3
34 18 18 'Supervisor+3' 4


Ideally, I would just pull an index out of the recursion (start at 0, and one at each step of the recursion), but I haven't found anything pointing me to how to do that. Is getting such an index from the recursion possible, or what is the best way to get this table sorted as I want it?

Answer

Feels a bit to short for an answer, but by request:

It would be easier to answer if you showed your recursive CTE, but you can just select 0 as SortOrder in your anchor, and in your recursive part select SortOrder + 1 as SortOrder.