Benjamin - 8 months ago 40
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?

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.