RSmith wrote:
> Clemens I'm liking the link list but did not go with it due to an expensive
> insert function
Yes, you have to update two references (with prev/next), but how is that
worse than the update of all SortOrder values?
> how would I get a normal SQL query ORDER BY clause to use that?
This is not possible with ORDER BY. You would need a recursive common
table expression:
WITH RECURSIVE ListInOrder(ID, Data, Next) AS (
SELECT ID, Data, Next
FROM ListTable
WHERE Prev IS NULL
UNION ALL
SELECT L.ID, L.Data, L.Next
FROM ListTable AS L
JOIN ListInOrder ON L.ID = ListInOrder.Next
)
SELECT ID, Data FROM ListInOrder;
The recursion stops when a Next value does not have a corresponding ID.
See <http://www.sqlite.org/lang_with.html>.
> I'd have to have an Index on "Next"
To be efficient, the query above needs just an index on ID, and an index
for the first list entry. If the first entry has a special non-NULL
value (such as -1) instead of Prev=NULL, it is be possible to use
a partial index containing just this one entry:
CREATE INDEX ListTable_first ON ListTable(Prev) WHERE Prev = -1;
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
