On Sun, Feb 14, 2016 at 07:31:43PM -0500, Richard Hipp wrote:
> On 2/14/16, Gary Briggs <chunky at icculus.org> wrote:
> >
> > "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."
> >
>
> Untested code follows.
I appreciate all the help I can get, at this point :-)
> I'l build up the answer in stages. First, a query that finds the
> entry with 25 of @x, at y that as the minimum "val" with distance as a
> tie-breaker.
>
> SELECT id
> FROM foo
> WHERE distance(@x, at y,x,y)<=25
> ORDER BY val, distance(@x, at y,x,y)
> LIMIT 1;
Yeah, this is what my current "solution" converged on [where I do n+1
queries in code, instead of having it all in a single query].
> For every row, find the ID of the row in the same table within 25 of
> the row and with the minumum val, distance. (This is almost what you
> want, but only returns the IDs of the rows, not the full row.)
>
> 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.
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
Thank-you!
Gary
