Keith. You are correct. The online help is the only design document. Furthermore, from my observations about this forum, the intrepid Dr. Hipp will often never weigh in to disclose the full design or thought process of the intended product behavior.
You can put the arbitrary outer column expression scoping implementation for ORDER BY in the same category as the even more capricious outer column expression scoping implementation of the LIMIT clause. There are more examples. Another good one was the lack of general delimiter character setting somebody needed for wrangling mainframe data with the shell program. The proposed solution was to have the user tweak their local build so the improvement would never be propagated into the product for others to use! Still another recent good one was the lack of any explanation for a peculiar edge case of the equality operator when dealing with boolean 0/1 expressions and conversions from text. The guy who pointed out the problem gave some great examples including the one that doesn't work sensibly. Those examples ought to be in the online help. From this forum you'll definitely get a lot of replies about how something currently works in the SQLite language but not why the implementation was chosen or even if it is only by accident that it currently works in a certain peculiar non-orthogonal or non-symmetrical way. FYI, there is a workaround for the expression scoping limitations of ORDER BY. You may cast the desired ordering information into a synthetic column of an intermediate table and then pick up only the payload column of that table. Below I've restated your example in CTE form and added the requisite synthetic column "myorder" to an intermediate table. WITH t1(x,y) AS (VALUES (2,1),(3,2),(6,4)), t2(z) AS (VALUES (4)) SELECT (SELECT y FROM (SELECT y,abs(x-z)myorder FROM t1 ORDER BY myorder LIMIT 1))y FROM t2; Enjoy. Thanks for posting such a clearly stated problem. Peter On Sun, Dec 18, 2016 at 12:23 PM, Keith Maxwell <keith.maxw...@gmail.com> wrote: > Keith > > Brilliant! Thank you. > > > Correlated (outer) columns are not permitted in the ORDER BY clause > > At least I've learnt a new term [1]. Reading the documentation again > [2], I think a > change to the paragraph below would make this clearer: > > 3. Otherwise, if the ORDER BY expression is any other expression, it is > evaluated and the returned value used to order the output rows. If the > SELECT > statement is a simple SELECT, then an ORDER BY may contain any arbitrary > expressions. However, if the SELECT is a compound SELECT, then ORDER BY > expressions that are not aliases to output columns must be exactly the > same as > an expression used as an output column. > > Maybe add a sentence: "If the SELECT is a correlated inner subquery then > the > ORDER BY expression may not include columns from the outer query." > > I'm also not sure if it would help to drop the word "any" from "any > arbitrary > expressions"? > > I'm afraid I didn't get very far trying to look up a standard. > > I appreciate your help! Thanks again. > > Kind Regards > > Keith Maxwell > > [1] https://en.wikipedia.org/wiki/Correlated_subquery > [2] https://www.sqlite.org/lang_select.html#order-by > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
