SQLite Forum

How can I maintain a list in an arbitrary order?
Login
Given an arbitrarily ordered list of items b,q,t,z,a (obviously not in sort order), how can I insert a new item "p" after item "t", for example, and have a query return the ordered list b,q,t,p,z,a?

If I use a position integer, then all the positions after the inserted row need to be increased by one. Ugh.

The table below seems to capture the information. However, I can't figure out how to query to get it in the right order. My guess is I need a recursive CTE.

Can someone help me out here, please?

``` sql
--drop table list;
create table list (row_id INTEGER PRIMARY KEY, item TEXT UNIQUE,
     prev INTEGER REFERENCES list(row_id) DEFERRABLE INITIALLY DEFERRED);
insert into list values(null,"b",0);
insert into list values(null,"q",(select last_insert_rowid()));
insert into list values(null,"t",(select last_insert_rowid()));
insert into list values(null,"z",(select last_insert_rowid()));
insert into list values(null,"a",(select last_insert_rowid()));
select * from list order by row_id;
row_id|item|prev
1|b|0
2|q|1
3|t|2
4|z|3
5|a|4
-- want order to be b,q,t,z,a

-- normal insert "p" before "t"
begin transaction;
insert into list values(null,"p",(select prev from list where item="t"));
update list set prev=(select last_insert_rowid()) where item="t";
commit transaction;
select * from list order by row_id;
row_id|item|prev
1|b|0
2|q|1
3|t|6
4|z|3
5|a|4
6|p|2
-- want order to be b,q,p,t,z,a

-- boundary insert row before first row ("b")
begin transaction;
insert into list values(null,"w",(select prev from list where item="b"));
update list set prev=(select last_insert_rowid()) where item="b";
commit transaction;
select * from list order by row_id;
row_id|item|prev
1|b|7
2|q|1
3|t|6
4|z|3
5|a|4
6|p|2
7|w|0
-- want order to be w,b,q,p,t,z,a

-- boundary insert new row at end "h"
insert into list values(null,"h",(select max(row_id) from list));
select * from list order by row_id;
row_id|item|prev
1|b|7
2|q|1
3|t|6
4|z|3
5|a|4
6|p|2
7|w|0
8|h|7-- want order to be w,b,q,p,t,z,a,h
```