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.
