RSmith wrote: > I have one program that inserts values to a table and determine sort > order using one standard trick that has a REAL column named > "SortOrder" [...] > reassign SortOrders simply in Integer steps: 1, 2, 3 etc. > > ID | SortOrder | Data > 1 | 1 | 'First Row' > 2 | 4 | 'Eventual Fourth Row' > 3 | 5 | 'Last Row' > 4 | 2 | 'New Second Row' > 5 | 3 | 'New Third Row'
When we think of this not as a relational table but as a data structure in memory, this is the equivalent of an array, where SortOrder is the array index. Inserting or deleting of an entry requires moving all following entries. However, an array would not be the most efficient data structure for this application, because random access to the n-th entry is not needed, and the order of one entry is only relative to the adjacent entries. A better data structure would be a linked list. As a table, a linked list would be modelled as follows: ID | Prev | Next | Data 1 | | 4 | 'First Row' 2 | 5 | 3 | 'Eventual Fourth Row' 3 | 2 | | 'Last Row' 4 | 1 | 5 | 'New Second Row' 5 | 4 | 2 | 'New Third Row' This makes insertion easier, and should not need more space than a floating-point SortOrder (because the prev/next pointers are integers), but now reading the list in order requires a recursive CTE. Well, I'm not sure if this is more clever than useful ... Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
