OK, I get it now. You need a whole query to calculate a value for val, and then another query to find the lowest distance match on val. You can't do that with simple correlated queries or subqueries, except by repeating a lot of the work.
The only reasonable prospect I could see for efficiency is to use a CTE to calculate the intermediate table containing VAL and DIST<25, a main query for the final result with a value subquery to compute min(VAL). Or you could use two queries and an explicit temporary table. The reason for my interest is how to write this query in Andl, and since on Sqlite Andl generates SQL I'd like to check that the SQL actually works. My solution for this problem requires storing the intermediate results, which is trivial in Andl and much harder to do in SQL. 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 11:47 AM To: SQLite mailing list <sqlite-users at mailinglists.sqlite.org> Subject: Re: [sqlite] Correlated subquery throwing an error On Mon, Feb 15, 2016 at 11:11:26AM +1100, david at andl.org wrote: > 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 That specific SQL gives the error: "only a single result allowed for a SELECT that is part of an expression" But when I removed the "id" column from the inner select, it worked. It's when I replace "d" with the full DISTANCE() in just the ORDER BY clause that it stops working. Works: SELECT foo.*, (SELECT DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0) AS d FROM foo AS nearest WHERE d < 25 ORDER BY val, d LIMIT 1) AS id2 FROM foo LIMIT 5 Works: SELECT foo.*, (SELECT DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0) AS d FROM foo AS nearest WHERE DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0) < 25 ORDER BY val, d LIMIT 1) AS id2 FROM foo LIMIT 5 Doesn't work ["no such column: foo.x"]: SELECT foo.*, (SELECT DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0) AS d FROM foo AS nearest WHERE d < 25 ORDER BY val, DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0) LIMIT 1) AS id2 FROM foo LIMIT 5 Thanks, Gary > Regards > David M Bennett FACS > > Andl - A New Database Language - andl.org > > -----Original Message----- > From: sqlite-users-bounces at 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-w > ithout > -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 > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
