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
