SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE items (`id` INTEGER, `item_name` VARCHAR(6), `parent_id` INTEGER, `list_order` INTEGER);
INSERT INTO items (`id`, `item_name`, `parent_id`, `list_order`) VALUES

('1',    'Pois',     '0',      '4'),
('2',    'Então',    '0',      '5'),
('3',    'Teste',    '0',      '3'),
('11',   'Teste 3',  '3',      '2'),
('12',   'Teste 2',  '3',      '0'),
('13',   'Teste 1',  '3',      '1'),
('4',    'Fundo',    '0',      '2'),
('7',    'Profundo', '4',      '1'),
('8',    'Dhdhd',    '4',      '0'),
('9',    'Gagagaga', '8',      '1'),
('10',   'Fsfsfsfs', '8',      '0'),
('5',    'Profundo ','0',      '1'),
('6',    'Gigante', '0',      '6'),
('14',   'Teste',    '0',      '0');
WITH 
  levels AS (
    SELECT *, 0 lvl FROM items
    UNION ALL
    SELECT i.*, l.lvl + 1 
    FROM items i INNER JOIN levels l
    ON l.id = i.parent_id 
    ORDER BY list_order ASC
  ),
  row_numbers AS (
    SELECT id, item_name, parent_id, MAX(lvl) lvl,
           (SELECT COUNT(*) FROM levels l2 
            WHERE l2.parent_id = l1.parent_id AND l2.lvl = l1.lvl AND l2.id <= l1.id 
            ORDER BY l2.lvl, l2.list_order ) rn, 
            list_order
    FROM levels l1
    GROUP BY id, item_name, parent_id, list_order
    ORDER BY list_order ASC
  ),
  cte AS (
    SELECT id, item_name, parent_id, lvl, rn || '' path_index, list_order
    FROM row_numbers
    UNION ALL
    SELECT r.id, r.item_name, r.parent_id, r.lvl, 
           c.path_index || '.' || r.rn, r.list_order
    FROM row_numbers r INNER JOIN cte c
    ON r.parent_id = c.id 
  )
SELECT * 
FROM cte  
GROUP BY id
HAVING MAX(LENGTH(path_index))
ORDER BY path_index + 0, path_index


id
item_name
parent_id
lvl
path_index
list_order


1
Pois
0
0
1
4

2
Então
0
0
2
5

3
Teste
0
0
3
3

11
Teste 3
3
1
3.1
2

12
Teste 2
3
1
3.2
0

13
Teste 1
3
1
3.3
1

4
Fundo
0
0
4
2

7
Profundo
4
1
4.1
1

8
Dhdhd
4
1
4.2
0

9
Gagagaga
8
2
4.2.1
1

10
Fsfsfsfs
8
2
4.2.2
0

5
Profundo
0
0
5
1

6
Gigante
0
0
6
6

14
Teste
0
0
7
0

id	item_name	parent_id	lvl	path_index	list_order
1	Pois	0	0	1	4
2	Então	0	0	2	5
3	Teste	0	0	3	3
11	Teste 3	3	1	3.1	2
12	Teste 2	3	1	3.2	0
13	Teste 1	3	1	3.3	1
4	Fundo	0	0	4	2
7	Profundo	4	1	4.1	1
8	Dhdhd	4	1	4.2	0
9	Gagagaga	8	2	4.2.1	1
10	Fsfsfsfs	8	2	4.2.2	0
5	Profundo	0	0	5	1
6	Gigante	0	0	6	6
14	Teste	0	0	7	0