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

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1759414224, '216.73.216.42', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/SQLite/sql/with/recursive/order-sibilings(118): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78

SQLite: recursive query - order siblings

See also