Like our Facebook page to get latest technology news!

User-defined Order in SQL

2018-03-21T05:22:48.4035519Z - / Hacker News

Some applications, such as todo lists, need to maintain a user-defined order of items.
The challenge is that the order is arbitrary and can change when the user rearranges items:This article investigates the best way to model the situation in SQL.
Storing row order should be compact on disk, and re-ordering items should use minimal CPU and I/O resources.
Suppose we want to insert a new “edit article” task between items 2 and 3.
insert into todos (pos, task) values (rational_intermediate( 2 , 3 ), 'edit article' ); select * from todos order by pos asc ; /* ┌─────────────────────┬─────┐ │ task │ pos │ ├─────────────────────┼─────┤ │ experiment with sql │ 1 │ │ write article │ 2 │ │ edit article │ 2.5 │ │ relax │ 3 │ │ repeat │ 4 │ └─────────────────────┴─────┘ */Floats created by rational_intermediate don’t seem to run up against precision problems like those generated by taking averages.