As stated, the Z does not exist to the inner query. The [ SELECT y FROM t1 ORDER BY abs(x - z) LIMIT 1 ] has no reference to what Z means. T2 may have it, but the inner query has no regards for the outer query.
On Sun, Dec 18, 2016 at 1:41 PM, Keith Maxwell <keith.maxw...@gmail.com> wrote: > Simon > > 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; > > There are workarounds or similar queries that produce the answer (an > example is below). I posted because I want to understand the error > message, or alternatively highlight the issue if the documentation > could be clearer or there is a bug. > > SELECT (SELECT y FROM (SELECT abs(x - z), y FROM t1 ORDER BY 1 LIMIT > 1)) FROM t2; > > Thank you but I don't immediately see how making the change you > suggest helps. I appreciate that in my example, which I've probably > over-simplified, there is only one value of z. In the more > complicated real life example there are lots of values of z so I can't > have `LIMIT 1` on the outer query. > > I appreciate your help! > > Kind Regards > > Keith Maxwell > > On 18 December 2016 at 18:20, Simon Slavin <slav...@bigfraud.org> wrote: > > > > On 18 Dec 2016, at 6:13pm, Keith Maxwell <keith.maxw...@gmail.com> > wrote: > > > >> I'm afraid I don't follow Simon. z is a column in t2. The sub-select > >> uses z in an expression in its order by clause: abs(x - z). What do > >> you mean by "your sub-select only refers to table t1"? > > > > Then perhaps instead of > > > >> SELECT (SELECT y FROM t1 ORDER BY abs(x - z) LIMIT 1) FROM t2; > > > > You want to change which one is the sub-select: > > > > SELECT y FROM t1 ORDER BY abs(x - (SELECT z FROM t2)) LIMIT 1; > > > > 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
