Hi,
I have two tables:
CREATE TABLE lines (ID INT, size INT, line GEOMETRY);
CREATE TABLE polygons (ID INT, size INT, polygon GEOMETRY);
The query I want to call is the following:
SELECT COUNT(*) FROM POLYGONS one JOIN (SELECT LINE FROM LINES ORDER BY Length(line) DESC LIMIT 1) two ON "Intersect"(one.polygon, two.line) = TRUE;
I tried out to create a function that replaces the use of the subquery:
create function line_max()
returns table (id integer)
begin
return select id from lines order by Length(line) desc limit 1;
end;
select count(*) From polygons one join lines two ON "Intersect"(one.polygon, two.line) = TRUE where two.id in (select id from line_max());
The query started without errors but it stops with the following error:
HEAPalloc: Insufficient space for HEAP of 7200000000 bytes.
Thanks
Leo
Hi Leo,
could you give an SQL example of the very query you'd like to run but that MonetDB "rejects"
due to order by and/or limit in a sub query?
Thanks!
Stefan
----- Original Message -----
Hi,
I understand why Monetdb does’t support ORDER BY in subqueries.
An ORDER BY combined with a LIMIT does influence the size of the query result
set.
Is there a workaround to use such a query?
Here’s my use case:
I’m working with the spatial extension and have a table filled with polygons.
Now I want to know the ids of all polygons that intersect the polygon with
the biggest area.
Is there a chance to answer this use case with only one query?
Regards
Leo
On 27 Oct 2014, at 19:02, Martin Kersten <Martin.Kersten@cwi.nl> wrote:
Hi
ORDER BY (and LIMIT/OFFSET for that matter) only control the overall
shape of the query result set. Hence, they are indeed not supported within
subqueries in MonetDB.
See thread
https://www.monetdb.org/pipermail/users-list/2013-October/006861.html
regards, Martin
On 27/10/14 17:57, Kotschenreuther, Leo wrote:
Hi all,
I tried to use some more complex queries, they contained subqueries. I
will shortly desrcibe the situation:
I have two tables (e.g. test and foo) and want to join them. Furthermore I
want to preselect a subset of one of the two tables by adding some
conditions (e.g. order the records by attribute c and only choosing the
biggest one).
A query could look like the this:
*sql>select * from test join (select * from foo order by c limit 1);*
*
*
The result I receive is the following:
*syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or
')' in: "select * from test join (select * from foo order**”*
*
*
On the other hand, I can run the subquery without the main query and I
receive a valid result.
Is there a possibility to use subqueries with an ORDER BY statement
without separating them into two independent queries?
Regards
Leo
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
--
|
Stefan.Manegold@CWI.nl | DB Architectures (DA) |
|
www.CWI.nl/~manegold/ | Science Park 123 (L321) |
| +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
_______________________________________________
users-list mailing list
users-list@monetdb.orghttps://www.monetdb.org/mailman/listinfo/users-list