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
