Why not SELECT foo.*, (SELECT id, DIST(foo.x, foo.y, nearest.x, nearest.y) AS d FROM foo AS nearest WHERE d < 25 ORDER BY val, d LIMIT 1) AS id2 FROM foo
Regards David M Bennett FACS Andl - A New Database Language - andl.org -----Original Message----- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Gary Briggs Sent: Monday, 15 February 2016 10:43 AM To: sqlite-users at mailinglists.sqlite.org Subject: [sqlite] Correlated subquery throwing an error I posted a question on stackoverflow, here: http://stackoverflow.com/questions/35382897/implementing-a-sql-query-without -window-functions In short, I have a table that I'm trying to query: CREATE TABLE foo ( id INTEGER PRIMARY KEY, x REAL NOT NULL, y REAL NOT NULL, val REAL NOT NULL, UNIQUE(x,y)); I have a helper function, DISTANCE(x1,y1,x2,y2). The results I'm looking for are: "For every row in that table, I want the entire row in that same table within a certain distance [eg 25], with the lowest "val". For rows with the same "val", I want to use lowest distance as a tie breaker." The answer I got on stackoverflow included this correlated subquery, but it's not working: SELECT foo.*, (SELECT id FROM foo AS nearest WHERE DIST(foo.x, foo.y, nearest.x, nearest.y) < 25 ORDER BY val, DIST(foo.x, foo.y, nearest.x, nearest.y) LIMIT 1) AS id2 FROM foo I get the error "no such column: foo.x" I tried rewriting it to have the subquery in a WHERE clause instead [not quite the same query, but I think the problem I have is the same] SELECT outerfoo.* FROM foo outerfoo WHERE outerfoo.id=(SELECT id FROM foo AS nearest WHERE DISTANCE(outerfoo.x, outerfoo.y, nearest.x, nearest.y) <= 25 ORDER BY val, DISTANCE(outerfoo.x, outerfoo.y, nearest.x, nearest.y) LIMIT 1) And now I get the error "no such column: outerfoo.x" Can anyone advise on what I might do? Thank-you very much, Gary _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
