On Mon, Feb 15, 2016 at 11:11:26AM +1100, david at andl.org wrote:
> Why not
>
> SELECT foo.*,
> (SELECT id, DIST(foo.x, foo.y, nearest.x, nearest.y) AS d
> FROM foo AS nearest
> WHERE d < 25
> ORDER BY val, d
> LIMIT 1) AS id2
> FROM foo
That specific SQL gives the error:
"only a single result allowed for a SELECT that is part of an expression"
But when I removed the "id" column from the inner select, it worked. It's
when I replace "d" with the full DISTANCE() in just the ORDER BY clause
that it stops working.
Works:
SELECT foo.*,
(SELECT DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0) AS d
FROM foo AS nearest
WHERE d < 25
ORDER BY val, d
LIMIT 1) AS id2
FROM foo
LIMIT 5
Works:
SELECT foo.*,
(SELECT DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0) AS d
FROM foo AS nearest
WHERE DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0) < 25
ORDER BY val, d
LIMIT 1) AS id2
FROM foo
LIMIT 5
Doesn't work ["no such column: foo.x"]:
SELECT foo.*,
(SELECT DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0) AS d
FROM foo AS nearest
WHERE d < 25
ORDER BY val, DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0)
LIMIT 1) AS id2
FROM foo
LIMIT 5
Thanks,
Gary
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org
>
> -----Original Message-----
> From: sqlite-users-bounces at mailinglists.sqlite.org
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Gary
> Briggs
> Sent: Monday, 15 February 2016 10:43 AM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: [sqlite] Correlated subquery throwing an error
>
> I posted a question on stackoverflow, here:
> http://stackoverflow.com/questions/35382897/implementing-a-sql-query-without
> -window-functions
>
> In short, I have a table that I'm trying to query:
> CREATE TABLE foo (
> id INTEGER PRIMARY KEY,
> x REAL NOT NULL,
> y REAL NOT NULL,
> val REAL NOT NULL,
> UNIQUE(x,y));
>
> I have a helper function, DISTANCE(x1,y1,x2,y2).
> The results I'm looking for are:
>
> "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."
>
> The answer I got on stackoverflow included this correlated subquery, but
> it's not working:
> SELECT foo.*,
> (SELECT id
> FROM foo AS nearest
> WHERE DIST(foo.x, foo.y, nearest.x, nearest.y) < 25
> ORDER BY val, DIST(foo.x, foo.y, nearest.x, nearest.y)
> LIMIT 1) AS id2
> FROM foo
>
> I get the error "no such column: foo.x"
>
> I tried rewriting it to have the subquery in a WHERE clause instead [not
> quite the same query, but I think the problem I have is the same]
>
> SELECT outerfoo.*
> FROM foo outerfoo
> WHERE outerfoo.id=(SELECT id
> FROM foo AS nearest
> WHERE DISTANCE(outerfoo.x, outerfoo.y, nearest.x, nearest.y) <= 25
> ORDER BY val, DISTANCE(outerfoo.x, outerfoo.y, nearest.x, nearest.y)
> LIMIT 1)
>
> And now I get the error "no such column: outerfoo.x"
>
> Can anyone advise on what I might do?
>
> Thank-you very much,
> Gary
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
--
