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
