Simon, Stephen thank you for trying to help. 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"?
> SELECT (13, 3, 94) FROM t2 `LIMIT 1` on the sub-query means that it won't return more than one row; further z does exist to the inner query, as I've tried to show in the related example below: CREATE TABLE t1(x INTEGER, y INTEGER); INSERT INTO t1(x, y) VALUES(2, 1), (3, 2), (6, 4); CREATE TABLE t2(z INTEGER); INSERT INTO t2(z) VALUES(4); SELECT (SELECT z FROM t1 LIMIT 1) FROM t2; 4 4 To try and explain what the query was written to do in English: I have a list pairs of numbers x and y. I want to find the y corresponding to the x that is closest to each value of z. There are plenty of workarounds that I can use, but to me the query as written is valid, and I don't understand why I get "no such column". I can try and clean up a more real life example, but I think that makes it harder to follow by adding in date functions. Thanks for your help. Kind Regards Keith Maxwell On 18 December 2016 at 15:52, Stephen Chrzanowski <pontia...@gmail.com> wrote: > Your inner query only deals with what I've left in the quote below. Z > doesn't exist to the inner query. > > On Sun, Dec 18, 2016 at 8:57 AM, Keith Maxwell <keith.maxw...@gmail.com> > wrote: > >> I've read the documentation[1] a few times and I don't understand why I >> get: >> CREATE TABLE t1(x INTEGER, y INTEGER); >> SELECT (SELECT y FROM t1 ORDER BY abs(x - z) LIMIT 1) FROM t2; >> > _______________________________________________ > 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
