TechNewsHeaven {{source.name}}




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.

Your favorite news


Popular news




Recommended news