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
