I have a table that looks like this:

CREATE TABLE tbl (
    id int,
    parent_id int,
    child_order int,
    name varchar(20)
)

Take this as a sample dataset:

1   NULL    0   BtVS
2   NULL    1   Star Trek
3   1       0   Buffy
4   1       1   Willow
5   1       2   Xander
6   1       3   Giles
7   2       0   Kirk
8   2       1   Spock
9   2       2   McCoy
10  2       3   Uhura
11  3       0   Strength
12  3       1   Speed
13  3       2   Dreams
14  4       0   Tech
15  4       1   Magic
16  4       2   Dreams
17  5       0   Heart
18  5       1   Humor
19  5       2   Loyalty
20  6       0   Wisdom
21  6       1   Training
22  6       2   Guidance
23  7       0   Leadership
24  7       1   Independence
25  7       2   Courage
26  8       0   Logic
27  8       1   Strength
28  8       2   Nerve Pinch
29  9       0   Healing
30  9       1   Sarcasm
31  9       2   Friendship
32  10      0   Communications
33  10      1   Languages
34  10      2   Music

I want it to output like this:

1   1   BtVS
3   2       Buffy
11  3           Strength
12  3           Speed
13  3           Dreams
4   2       Willow
14  3           Tech
15  3           Magic
16  3           Dreams
5   2       Xander
17  3           Heart
18  3           Humor
19  3           Loyalty
6   2       Giles
20  3           Wisdom
21  3           Training
22  3           Guidance
2   1   Star Trek
7   2       Kirk
23  3           Leadership
24  3           Independence
25  3           Courage
8   2       Spock
26  3           Logic
27  3           Strength
28  3           Nerve Pinch
9   2       McCoy
29  3           Healing
30  3           Sarcasm
31  3           Friendship
10  2       Uhura
32  3           Communications
33  3           Languages
34  3           Music

Giving me the id field, the report level, and the name field. But in order, like this. The child_order field may be discontinuous. Meaning that there might be 4 children of a single parent with child_orders of 0, 1, 5 and 9. It’s only their relative order that matters. And there can be more than 10 children, so I can’t rely on the number of digits in the child_order

I’ve tried working with CTEs, but I haven’t found a way to make it work with multiple roots, and I haven’t found a way to make it work with the child_order column, because ORDER BY is not valid inside a CTE.

Is there a way to accomplish this that’s reasonable? I’ve thought about using powers of 10 for each report level, and adding the hierarchy to get a sort value, but like I said, you can have two digit child_orders, which would mess that up.