Correlated (outer) columns are not permitted in the ORDER BY clause -- only items from the SELECT list (or from the directly selected tables). Normally in a correlated subquery the outer columns are only (designed to be) used in the WHERE clause of the correlated subquery, although they may be accessed in the SELECT list.
In other words, the SELECT ... FROM ... ORDER BY <list of ordinal column positions> will work because you are sorting the result-set by data contained within the result set of the subquery. I have no idea what the standard has to say about the usage of an outer column in a correlated subquery order by clause however. > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Keith Maxwell > Sent: Sunday, 18 December, 2016 12:14 > To: SQLite mailing list > Subject: Re: [sqlite] Unexpected 'no such column' with expression in > subquery ORDER BY > > Simon, Stephen > > Thank again. Did you try the other queries I've included? I've tried > to include two that show sub-queries using z from the main query. > > The simplest one (not a useful query, just illustrates a sub-query using > z): > > sqlite> SELECT (SELECT z FROM t1 LIMIT 1) FROM t2; > 4 > > And the workaround that produces the answer: > > sqlite> SELECT (SELECT y FROM (SELECT abs(x - z), y FROM t1 ORDER BY 1 > LIMIT 1)) FROM t2; > 2 > > I can't reconcile what you're saying about z in the sub-query with > what I see from the results above. > > A subtle point that might be getting lost is that I can use z > elsewhere in the sub-query, just not in its `ORDER BY` clause. > > I appreciate your continued help. > > Kind Regards > > Keith > > On 18 December 2016 at 18:51, Simon Slavin <slav...@bigfraud.org> wrote: > > > > On 18 Dec 2016, at 6:41pm, Keith Maxwell <keith.maxw...@gmail.com> > wrote: > > > >> Thanks again, maybe I didn't ask the question in the best way: why > >> with the query below do I get "Error: no such column: z"? > >> > >> SELECT (SELECT y FROM t1 ORDER BY abs(x - z) LIMIT 1) FROM t2; > > > > Because the inner SELECT does not have access to the details from the > outer SELECT. It does not know what 'z' is. None of the tables in the > inner SELECT have a column called 'z'. > > > > This works: > > > > SELECT y FROM t1 ORDER BY abs(x - (SELECT z FROM t2)) LIMIT 1; > > > > though I do not know if it does what you want it to do. > > > > Simon. > > _______________________________________________ > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
