SQLite Forum

What is the best way to have a custom order?
Login

What is the best way to have a custom order?

(1) By Yuzem (yuzem1993) on 2021-01-17 14:00:25 [link] [source]

Just for the example:

CREATE TABLE example (
    id   	INTEGER PRIMARY KEY,
    sort	INTEGER
);

Lets say both columns have the same values, numbers from 0 to 100:
id   pos
0   0
1   1
2   2
3   3
etc...

Suppose I want to change id 98 to position 2:
UPDATE example SET pos = 2 WHERE id = 98;
UPDATE example SET pos = pos+1 WHERE id != 98 AND pos => 2 AND pos < 98;

Is there any better way?

(2) By Larry Brasfield (LarryBrasfield) on 2021-01-17 14:46:21 in reply to 1 [link] [source]

I have found, in circumstances where I needed a field just for ordering, that using a floating point value eliminates (or postpones for a long time) the need to mess with rows for which the ordering is not changing. Until those values have quite a few decimal places, it is always possible to find a value between a pair of (differing) neighboring values.

(4) By Yuzem (yuzem1993) on 2021-01-17 20:53:54 in reply to 2 [link] [source]

Thank you.
I have thought about that possibility, but it is good to know it is used.
In the previous example of moving id 98 to position 2, I think I would need something like this:

UPDATE example SET pos = (SELECT MAX(pos)+0.001) WHERE pos < 3;

Correct me if I am wrong or if there is a better way.

(3) By doug (doug9forester) on 2021-01-17 17:00:35 in reply to 1 [link] [source]

I use a single direction linked list. This implementation requires a dummy entry for position 0 but works well for me.

CREATE TABLE teachertable (
teacher_id INTEGER NOT NULL PRIMARY KEY,
teacher_displayname TEXT NOT NULL UNIQUE,
teacher_prev INTEGER NOT NULL REFERENCES teachertable(teacher_id)
);
I insert new rows in the table within a transaction which inserts a new row and updates the link to to put the item where it belongs.

(5) By Yuzem (yuzem1993) on 2021-01-17 20:57:45 in reply to 3 [link] [source]

Thank you, but I think I don't quite understand.
Are you saving relative positions to the previous items in the list?
If so, in case of moving an item, wouldn't all the next items be moved also?

(6) By Larry Brasfield (LarryBrasfield) on 2021-01-18 02:39:09 in reply to 5 [link] [source]

With the linked list, it is only necessary to adjust the prior or subsequent neighboring nodes' links. For a singly-linked list, all nodes which continue to have the same successor remain untouched after an insertion or deletion.

(8) By Yuzem (yuzem1993) on 2021-01-18 14:46:41 in reply to 6 [link] [source]

I ended up reading about linked lists. In my opinion, it is a bit complicated to get the sorted list. But it is an interesting approach nevertheless. Thanks

(7) By niklasb on 2021-01-18 13:18:35 in reply to 1 [link] [source]

Here's an article suggesting various ways:

https://begriffs.com/posts/2018-03-20-user-defined-order.html

Discussed on Hacker News recently:

https://news.ycombinator.com/item?id=25797674

(9) By Yuzem (yuzem1993) on 2021-01-18 14:47:02 in reply to 7 [source]

Interesting article, thank you!