Sqlite recursive ORDER BY

Question

I have a table like this:

CREATE TABLE "tester" (
    "id"    INTEGER UNIQUE,
    "pid"   INTEGER,
    "pos"   INTEGER,
    "nm"    VARCHAR(255),
    PRIMARY KEY("id" AUTOINCREMENT)
);

INSERT INTO 'tester' (id, pid, pos, nm)
VALUES (1,  0, 0, "My Tester"),
       (2, 1, 0, "cat1"),
       (4, 9, 1, "subcat1"),
       (9, 1, 1, "cat2"),
       (10, 2, 0, "subcat3"),
       (13, 2, 1, "subcat2"),
       (14, 9, 0, "subcat4"),
       (15, 4, 0, "data1"),
       (16, 4, 1, "data2"),
       (17, 13, 0, "data3"),
       (18, 13, 1, "data4"),
       (19, 10, 0, "data5"),
       (20, 10, 1, "data6"),
       (21, 14, 0, "data7"),
       (22, 14, 1, "data8");

This represents a tree like this:

I want to run query to get all from "data1" till "data8" ordered by pos.

I have the ids of all nodes from "data1" till "data8" but they are not in order.

I tried :

SELECT * 
FROM "tester" 
WHERE id IN ("15,16,17,18,19,20,21,22") 
ORDER BY pos ASC

But the result is not as expected because the parent's positions are different.

The expected result is:

data5
data6
data3
data4
data7
data8
data1
data2

Answer 1

You can use a recursive common table expression to visit all records by following parent-child relationships. Use order by to make sure that a node's depth is given priority, or when nodes have the same depth, that pos defines the order of visiting them.

From the CTE you can then select what you need, filtering records by the desired id and selecting the names:

with recursive depth_first(id, pos, nm, level) as (

    select   id, pos, nm, 0
    from     "tester"
    where    pid = 0

    union all

    select   t.id, t.pos, t.nm, depth_first.level+1
    from     "tester" t
    join     depth_first 
          on t.pid = depth_first.id
    order by 4 desc, 2
)
select nm 
from   depth_first
where  id between 15 and 22;