Search notes:

SQLite: recursive query - order siblings

The following sql snippets try to demonstrate how SQLite allows to hierarchically select a result set where siblings (records with the same parent record) are ordered by a specific criteria.

This is the table that we're going to select from.

id is the primary key.

parent is a record's foreign key to its parent.

pos is the ordering of records with the same parent.

Since we want the ordering of the siblings to be determinated, we place a unique constraint for parent and pos.

drop table if exists item;

create table item (
  id       integer not null primary key,
  parent   integer     null references item,
  txt      text    not null,
  pos      integer not null,
  unique(parent, pos)
);

Github repository about-sqlite, path: /sql/with/recursive/order-siblings/create-table.sql

Some records for the table:

insert into item values ( 1, null, 'B'    , 2);
insert into item values (12,    1, 'B 2'  , 2);
insert into item values ( 9, null, 'C'    , 3);
insert into item values ( 4,    1, 'B 3'  , 3);
insert into item values (16,    1, 'B 1'  , 1);
insert into item values ( 2,    9, 'C 2'  , 2);
insert into item values ( 7, null, 'A'    , 1);
insert into item values ( 6,    9, 'C 1'  , 1);
insert into item values (10,    6, 'C 1 a', 1);
insert into item values (27,   12, 'B 2 c', 3);
insert into item values (15,   16, 'B 1 b', 2);
insert into item values (23,    7, 'A 2'  , 2);
insert into item values (20,    6, 'C 1 b', 2);
insert into item values ( 5,   12, 'B 2 a', 1);
insert into item values (19,    7, 'A 1'  , 1);
insert into item values (13,    7, 'A 3'  , 3);
insert into item values ( 3,   12, 'B 2 b', 2);
insert into item values (14,   16, 'B 1 a', 1);
insert into item values (11,   16, 'B 1 c', 3);

Github repository about-sqlite, path: /sql/with/recursive/order-siblings/insert.sql

The hierarchical query.

The «trick» to achieve the desired ordering of the siblings is to order the level descending and the position ascending:

with recursive items (lvl, id, txt, pos) as (
    select
       0     as lvl,
       id,
       txt,
       pos   as pos
    from
       item
    where
       parent is null
  union all
    select
      items.lvl + 1,
      item.id,
      item.txt,
      item.pos
    from
      items    join
      item  on items.id = item.parent
    order by
      items.lvl+1 desc,
      item.pos
)
select
   substr('_________________', 1, lvl*2) || txt
from
   items;

Github repository about-sqlite, path: /sql/with/recursive/order-siblings/select.sql

SQLite: recursive query - order siblings

See also