Order a nested table with path index according to list_order in SQLite

I have a SQL table with this nested structure:

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

My SQLite version does not support window function, currently my query at link SQLite query are returning as follow the result, but it's not ordered according to list_order.

   id      name_item   parent_id  level  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     14       Fsfsfsfs       8          2     4.2.2       0     5        Profundo       0          0     5           1     6        Gigante        0          0     6           6     86       Teste          0          0     7           0  

What I want the path_index column to follow list_order column, like here:

   id      name_item   parent_id  level  path_index list_order  ---------------------------------------------------------------     86       Teste          0          0     1           0     5        Profundo       0          0     2           1     4        Fundo          0          0     3           2     8        Dhdhd          4          1     3.1         0     14       Fsfsfsfs       8          2     3.1.1       0     9        Gagagaga       8          2     3.1.2       1     7        Profundo       4          1     3.2         1     3        Teste          0          0     4           3     12       Teste 2        3          1     4.1         0     13       Teste 1        3          1     4.2         1     11       Teste 3        3          1     4.3         2     1        Pois           0          0     5           4     2        Então          0          0     6           5     6        Gigante        0          0     7           6  

How can I align the path_index sorted according to list_order and level columns?

https://stackoverflow.com/questions/67309754/order-a-nested-table-with-path-index-according-to-list-order-in-sqlite April 29, 2021 at 09:06AM