Benjamin Benjamin - 2 months ago 17
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)
(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
. 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?


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.