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 |