{"id":31351,"date":"2021-01-18T15:48:28","date_gmt":"2021-01-18T20:48:28","guid":{"rendered":"https:\/\/mjtsai.com\/blog\/?p=31351"},"modified":"2021-01-18T15:50:28","modified_gmt":"2021-01-18T20:50:28","slug":"user-defined-order-in-sql","status":"publish","type":"post","link":"https:\/\/mjtsai.com\/blog\/2021\/01\/18\/user-defined-order-in-sql\/","title":{"rendered":"User-defined Order in SQL"},"content":{"rendered":"<p><a href=\"https:\/\/begriffs.com\/posts\/2018-03-20-user-defined-order.html\">Joe Nelson<\/a> (via <a href=\"https:\/\/news.ycombinator.com\/item?id=16635440\">Hacker<\/a> <a href=\"https:\/\/news.ycombinator.com\/item?id=25797674\">News<\/a>):<\/p>\n<blockquote cite=\"https:\/\/begriffs.com\/posts\/2018-03-20-user-defined-order.html\"><p>The most natural first attempt is to add an auto-incrementing integer column to track each item&rsquo;s position[&#8230;] It requires updating a bunch of rows to insert one between others.<\/p>\n<p>[&#8230;]<\/p>\n<p>What if we store the position of each row using <code>float<\/code> or <code>numeric<\/code> values rather than <code>int<\/code> or <code>bigint<\/code>? This would allow squeezing new elements between others, rather than shifting items forward to make room. [&#8230;] However floating point numbers have limited precision.<\/p>\n<p>[&#8230;]<\/p>\n<p>Non-negative fractions actually form a binary tree, with every fraction (in lowest terms) appearing at a unique node. [&#8230;] The terms of these fractions are expressed in lowest terms and grow slowly at each insertion. For instance you can see from the tree diagram earlier that inserting between 1 and 0 toward 0 generates 1\/2, 1\/3, 1\/4 &#8230; which can go a very long time because numerators and denominators in pg_rational each get 32 bits of storage.<\/p><\/blockquote>\n\n<p>Other approaches:<\/p>\n<ul>\n<li><a href=\"https:\/\/news.ycombinator.com\/item?id=25799472\">Using a string column<\/a> lets you always add rows in between other rows without ever having to renumber the world because the strings can be arbitrarily long.<\/li>\n<li>A linked list is efficient for updating the rows but doesn&rsquo;t work well with partial fetching.<\/li>\n<\/ul>\n\n<p>Ordered relationships in Core Data seem to use the basic integer approach. I&rsquo;ve not used this feature much because it&rsquo;s always seemed risky to rely on it. For many years it was buggy, <code>NSOrderedSet<\/code> still isn&rsquo;t available in Swift, and CloudKit doesn&rsquo;t support ordered relationships.<\/p>\n\n<p>Previously:<\/p>\n<ul>\n<li><a href=\"https:\/\/mjtsai.com\/blog\/2015\/06\/18\/core-data-bugs\/\">Core Data Bugs<\/a><\/li>\n<\/ul>","protected":false},"excerpt":{"rendered":"<p>Joe Nelson (via Hacker News): The most natural first attempt is to add an auto-incrementing integer column to track each item&rsquo;s position[&#8230;] It requires updating a bunch of rows to insert one between others. [&#8230;] What if we store the position of each row using float or numeric values rather than int or bigint? This [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"apple_news_api_created_at":"2021-01-18T20:48:33Z","apple_news_api_id":"56e277bc-ff68-4a5e-85c7-84434fa948e8","apple_news_api_modified_at":"2021-01-18T20:50:31Z","apple_news_api_revision":"AAAAAAAAAAAAAAAAAAAAAA==","apple_news_api_share_url":"https:\/\/apple.news\/AVuJ3vP9oSl6Fx4RDT6lI6A","apple_news_coverimage":0,"apple_news_coverimage_caption":"","apple_news_is_hidden":false,"apple_news_is_paid":false,"apple_news_is_preview":false,"apple_news_is_sponsored":false,"apple_news_maturity_rating":"","apple_news_metadata":"\"\"","apple_news_pullquote":"","apple_news_pullquote_position":"","apple_news_slug":"","apple_news_sections":"\"\"","apple_news_suppress_video_url":false,"apple_news_use_image_component":false,"footnotes":""},"categories":[4],"tags":[289,916,69,109,143,31,1837,30,1891,71,677,901],"class_list":["post-31351","post","type-post","status-publish","format-standard","hentry","category-programming-category","tag-algorithm","tag-cloudkit","tag-cocoa","tag-coredata","tag-database","tag-ios","tag-ios-14","tag-mac","tag-macos-11-0","tag-programming","tag-sql","tag-swift-programming-language"],"apple_news_notices":[],"_links":{"self":[{"href":"https:\/\/mjtsai.com\/blog\/wp-json\/wp\/v2\/posts\/31351","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mjtsai.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mjtsai.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mjtsai.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mjtsai.com\/blog\/wp-json\/wp\/v2\/comments?post=31351"}],"version-history":[{"count":1,"href":"https:\/\/mjtsai.com\/blog\/wp-json\/wp\/v2\/posts\/31351\/revisions"}],"predecessor-version":[{"id":31352,"href":"https:\/\/mjtsai.com\/blog\/wp-json\/wp\/v2\/posts\/31351\/revisions\/31352"}],"wp:attachment":[{"href":"https:\/\/mjtsai.com\/blog\/wp-json\/wp\/v2\/media?parent=31351"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mjtsai.com\/blog\/wp-json\/wp\/v2\/categories?post=31351"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mjtsai.com\/blog\/wp-json\/wp\/v2\/tags?post=31351"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}