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
