[sqlite] Correlated subquery throwing an error
On Mon, 15 Feb 2016 14:19:12 -0700 Scott Robison wrote: > Each job will take some amount of time to process. The order doesn't > matter as long as all jobs are eventually processed and you have a > single process running the jobs. Limit 1 is a reasonable way to grab > a single job. Reasonable, perhaps, but not logical. The logical approach is to use a feature in the data to select the "single job". One obvious way in your example would be to use min(jobid) or somesuch. --jkl
[sqlite] Correlated subquery throwing an error
On Mon, 15 Feb 2016 14:55:34 -0700 "Keith Medcalf" wrote: > Pretty sure you meant: > > select * from (select min(t) as t from T) as T; Yes, thanks. :-) --jkl
[sqlite] Correlated subquery throwing an error
On Tue, Feb 16, 2016 at 11:44 AM, James K. Lowden wrote: > On Mon, 15 Feb 2016 14:19:12 -0700 > Scott Robison wrote: > > > Each job will take some amount of time to process. The order doesn't > > matter as long as all jobs are eventually processed and you have a > > single process running the jobs. Limit 1 is a reasonable way to grab > > a single job. > > Reasonable, perhaps, but not logical. The logical approach is to use a > feature in the data to select the "single job". One obvious way in > your example would be to use min(jobid) or somesuch. > I have a hard time seeing into the future and understanding the implications of every problem statement in the world to know what the best solution would be to each and every one of them, so you could be right. :) Certainly there are algorithms in this world that depend on randomness as a feature. That being said, my self diagnosed OCD (my particular form is spelled COD, because it should be as symmetrical as possible) would probably require that I pick a very specific deterministic row. Not necessarily because it was right, but so that I could sleep at night. -- Scott Robison
[sqlite] Correlated subquery throwing an error
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 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
[sqlite] Correlated subquery throwing an error
On 15 Feb 2016, at 8:58pm, James K. Lowden wrote: > Clemens Ladisch wrote: > >> I don't know why correlated subqueries cannot use values from the >> outer query in the ORDER BY or LIMIT clauses; > > ORDER BY is not part of SELECT! It's not a relational operator. > Per the SQL standard -- ORDER BY cannot appear in a subquery. It can > appear in only one place: as a kind of post-processor that determines > the order in which the rows are delivered to the client. And to build on that, if you cannot specify ORDER BY then you cannot expect LIMIT to do anything useful. Since you don't know which ones will be top in the list there's no point in picking just the top ones. It took me a long time to convince myself that SQL really was a language based on sets. And the elements of sets don't have any order. Simon.
[sqlite] Correlated subquery throwing an error
On Mon, 15 Feb 2016 08:56:35 +0100 Clemens Ladisch wrote: > I don't know why correlated subqueries cannot use values from the > outer query in the ORDER BY or LIMIT clauses; ORDER BY is not part of SELECT! It's not a relational operator. Per the SQL standard -- ORDER BY cannot appear in a subquery. It can appear in only one place: as a kind of post-processor that determines the order in which the rows are delivered to the client. I suspect that's why Postgres ORDER BY doesn't recognize column names in the enclosing scope. If the day ever comes when SQLite can remove language features, internal ORDER BY (and LIMIT) would be at the top of my list, along with double-quotes for strings. --jkl
[sqlite] Correlated subquery throwing an error
On Mon, 15 Feb 2016 10:39:31 +0100 Clemens Ladisch wrote: > > you need to explicitly limit a subquery that is a field and must > > only ever return 1 result if the where clause is ambiguous about it > > Not in SQLite. (It ignores superfluous rows, and returns NULL if > there are no rows.) Yes, but the right way to do is to use logic instead of brute force. Instead of select * from (select t from T order by t limit 1) as T; use select * from (select min(t) as t from T group by t) as T; The latter has the benefit that it can be easily modified to add COUNT(*) to the subquery, and check for perhaps erroneous cases where COUNT(*) > 1. --jkl
[sqlite] Correlated subquery throwing an error
On 02/15/2016 02:56 PM, Clemens Ladisch wrote: > Gary Briggs wrote: >>> SELECT >>>a.id AS a_id, >>>(SELECT b.id >>> FROM foo AS b >>> WHERE b.id!=a.id >>> AND distance(a.x,a.y,b.x,b.y)<=25 >>> ORDER BY b.val, distance(a.x,a.y,b.x,b.y)) >>> FROM foo AS a >> This is the bit that doesn't seem to work; having >> distance(a.x,a.y,b.x,b.y) in the ORDER BY clause on the inner query is >> what appears to cause the error that it can't find a.x, from the outer query. > I don't know why correlated subqueries cannot use values from the outer > query in the ORDER BY or LIMIT clauses; this does not look as if it were > by design. ORDER BY and GROUP BY cannot refer to columns from the outer select by design: http://www.sqlite.org/src/artifact/9f7ce3a3c087a?ln=1252-1253 I think this is an SQL standard thing. It's what Postgres does too. Which is probably why SQLite is this way. LIMIT and OFFSET expressions cannot refer to any columns, correlated or otherwise. Dan.
[sqlite] Correlated subquery throwing an error
Pretty sure you meant: select * from (select min(t) as t from T) as T; adding a group by will return the minimum value of T for each group of T which is equivalent to select distinct T from T min(t) group by t, max(t) group by t, avg(t) group by t, distinct t all return the same results. > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of James K. Lowden > Sent: Monday, 15 February, 2016 13:58 > To: sqlite-users at mailinglists.sqlite.org > Subject: Re: [sqlite] Correlated subquery throwing an error > > On Mon, 15 Feb 2016 10:39:31 +0100 > Clemens Ladisch wrote: > > > > you need to explicitly limit a subquery that is a field and must > > > only ever return 1 result if the where clause is ambiguous about it > > > > Not in SQLite. (It ignores superfluous rows, and returns NULL if > > there are no rows.) > > Yes, but the right way to do is to use logic instead of brute force. > Instead of > > select * from (select t from T order by t limit 1) as T; > > use > > select * from (select min(t) as t from T group by t) as T; > > The latter has the benefit that it can be easily modified to add > COUNT(*) to the subquery, and check for perhaps erroneous cases where > COUNT(*) > 1. > > --jkl > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Correlated subquery throwing an error
On Mon, Feb 15, 2016 at 2:05 PM, Simon Slavin wrote: > > On 15 Feb 2016, at 8:58pm, James K. Lowden > wrote: > > > Clemens Ladisch wrote: > > > >> I don't know why correlated subqueries cannot use values from the > >> outer query in the ORDER BY or LIMIT clauses; > > > > ORDER BY is not part of SELECT! It's not a relational operator. > > Per the SQL standard -- ORDER BY cannot appear in a subquery. It can > > appear in only one place: as a kind of post-processor that determines > > the order in which the rows are delivered to the client. > > And to build on that, if you cannot specify ORDER BY then you cannot > expect LIMIT to do anything useful. Since you don't know which ones will > be top in the list there's no point in picking just the top ones. > I think there is still a potential use for limit (though maybe there's another way I haven't considered). You have a table representing a job queue. Each job will take some amount of time to process. The order doesn't matter as long as all jobs are eventually processed and you have a single process running the jobs. Limit 1 is a reasonable way to grab a single job. -- Scott Robison
[sqlite] Correlated subquery throwing an error
On Mon, Feb 15, 2016 at 08:56:35AM +0100, Clemens Ladisch wrote: > Gary Briggs wrote: > >> SELECT > >> a.id AS a_id, > >> (SELECT b.id > >>FROM foo AS b > >>WHERE b.id!=a.id > >> AND distance(a.x,a.y,b.x,b.y)<=25 > >>ORDER BY b.val, distance(a.x,a.y,b.x,b.y)) > >> FROM foo AS a > > > > This is the bit that doesn't seem to work; having > > distance(a.x,a.y,b.x,b.y) in the ORDER BY clause on the inner query is > > what appears to cause the error that it can't find a.x, from the outer > > query. > > I don't know why correlated subqueries cannot use values from the outer > query in the ORDER BY or LIMIT clauses; this does not look as if it were > by design. > > Anyway, I got it to work with another indirection: > > SELECT foo.*, >(SELECT id > FROM (SELECT id, > x, > y, > foo.x AS foo_x, > foo.y AS foo_y, > val > FROM foo) > WHERE DIST(foo_x, foo_y, x, y) < 25 > ORDER BY val, DIST(foo_x, foo_y, x, y) > LIMIT 1 >) AS id2 > FROM foo Fantastic, thank-you. Does exactly what it says on the box! Gary
[sqlite] Correlated subquery throwing an error
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
[sqlite] Correlated subquery throwing an error
R Smith wrote: > you need to alias a subquery always when it is used in a join Not in SQLite. > you need to explicitly limit a subquery that is a field and must only > ever return 1 result if the where clause is ambiguous about it Not in SQLite. (It ignores superfluous rows, and returns NULL if there are no rows.) Regards, Clemens
[sqlite] Correlated subquery throwing an error
On 2016/02/15 3:01 AM, Gary Briggs wrote: > > This is the bit that doesn't seem to work; having > distance(a.x,a.y,b.x,b.y) in the ORDER BY clause on the inner query is > what appears to cause the error that it can't find a.x, from the outer query. > > It's not tied to it being a custom function; I can replace it with > something else silly and get the same problem: > > SELECT > a.id AS a_id, > (SELECT b.id > FROM foo AS b > WHERE b.id!=a.id >AND distance(a.x,a.y,b.x,b.y)<=25 > ORDER BY b.val, (b.x-a.x) > FROM foo AS a > > no such column: foo.x You are missing a closing bracket there, plus you need to alias a subquery always when it is used in a join (which isn't the case here, but in others you've shown it was omitted) plus you need to explicitly limit a subquery that is a field and must only ever return 1 result if the where clause is ambiguous about it, plus you cannot refer an outer-select in a subquery in the aggregate/ordering clauses because there is not a gauranteed 1-to-1 relation (I'm not sure if this is the standard saying so or not, but it doesn't work in any DB system I've used). You can get around it with CTE's and the like.
[sqlite] Correlated subquery throwing an error
Gary Briggs wrote: >> SELECT >> a.id AS a_id, >> (SELECT b.id >>FROM foo AS b >>WHERE b.id!=a.id >> AND distance(a.x,a.y,b.x,b.y)<=25 >>ORDER BY b.val, distance(a.x,a.y,b.x,b.y)) >> FROM foo AS a > > This is the bit that doesn't seem to work; having > distance(a.x,a.y,b.x,b.y) in the ORDER BY clause on the inner query is > what appears to cause the error that it can't find a.x, from the outer query. I don't know why correlated subqueries cannot use values from the outer query in the ORDER BY or LIMIT clauses; this does not look as if it were by design. Anyway, I got it to work with another indirection: SELECT foo.*, (SELECT id FROM (SELECT id, x, y, foo.x AS foo_x, foo.y AS foo_y, val FROM foo) WHERE DIST(foo_x, foo_y, x, y) < 25 ORDER BY val, DIST(foo_x, foo_y, x, y) LIMIT 1 ) AS id2 FROM foo Regards, Clemens
[sqlite] Correlated subquery throwing an error
On Sun, Feb 14, 2016 at 07:31:43PM -0500, Richard Hipp wrote: > On 2/14/16, Gary Briggs wrote: > > > > "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." > > > > Untested code follows. I appreciate all the help I can get, at this point :-) > I'l build up the answer in stages. First, a query that finds the > entry with 25 of @x, at y that as the minimum "val" with distance as a > tie-breaker. > > SELECT id > FROM foo > WHERE distance(@x, at y,x,y)<=25 > ORDER BY val, distance(@x, at y,x,y) > LIMIT 1; Yeah, this is what my current "solution" converged on [where I do n+1 queries in code, instead of having it all in a single query]. > For every row, find the ID of the row in the same table within 25 of > the row and with the minumum val, distance. (This is almost what you > want, but only returns the IDs of the rows, not the full row.) > > SELECT > a.id AS a_id, > (SELECT b.id >FROM foo AS b >WHERE b.id!=a.id > AND distance(a.x,a.y,b.x,b.y)<=25 >ORDER BY b.val, distance(a.x,a.y,b.x,b.y)) > FROM foo AS a This is the bit that doesn't seem to work; having distance(a.x,a.y,b.x,b.y) in the ORDER BY clause on the inner query is what appears to cause the error that it can't find a.x, from the outer query. It's not tied to it being a custom function; I can replace it with something else silly and get the same problem: SELECT a.id AS a_id, (SELECT b.id FROM foo AS b WHERE b.id!=a.id AND distance(a.x,a.y,b.x,b.y)<=25 ORDER BY b.val, (b.x-a.x) FROM foo AS a no such column: foo.x Thank-you! Gary
[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-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 > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users --
[sqlite] Correlated subquery throwing an error
On 2/14/16, Gary Briggs wrote: > > "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." > Untested code follows. I'l build up the answer in stages. First, a query that finds the entry with 25 of @x, at y that as the minimum "val" with distance as a tie-breaker. SELECT id FROM foo WHERE distance(@x, at y,x,y)<=25 ORDER BY val, distance(@x, at y,x,y) LIMIT 1; For every row, find the ID of the row in the same table within 25 of the row and with the minumum val, distance. (This is almost what you want, but only returns the IDs of the rows, not the full row.) SELECT a.id AS a_id, (SELECT b.id FROM foo AS b WHERE b.id!=a.id AND distance(a.x,a.y,b.x,b.y)<=25 ORDER BY b.val, distance(a.x,a.y,b.x,b.y)) FROM foo AS a Use the output of the previous query to find the full rows: SELECT a_id, c.* FROM foo AS c, (SELECT a.id AS a_id, (SELECT b.id FROM foo AS b WHERE b.id!=a.id AND distance(a.x,a.y,b.x,b.y)<=25 ORDER BY b.val, distance(a.x,a.y,b.x,b.y)) FROM foo AS a) WHERE c.id=b_id; I repeat: The above is untested. It is also, clearly, an O(N*N) algorithm at best. If performance is a secondary goal, an R-Tree index may be useful here. -- D. Richard Hipp drh at sqlite.org
[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
