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.
