Hi,
in case there is a single longest line in LINES, how about
SELECT COUNT(*) FROM POLYGONS one JOIN (SELECT LINE FROM LINES where length(line) = (select max(length(line)) from lines) ) two ON "Intersect"(one.polygon, two.line) = TRUE;
Or, if there is no single longest line in LINES, but ID is unique in lines (i.e., also LIMIT 1 would choose among all equally long longest lines one arbitrary, implementation dependent, possibly non-deterministic line)
SELECT COUNT(*) FROM POLYGONS one JOIN (SELECT LINE FROM LINES where ID = (select max(ID) from LINES where length(line) = (select max(length(line)) from lines) ) ) two ON "Intersect"(one.polygon, two.line) = TRUE;
?
Stefan
----- Original Message -----
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
On 05 Nov 2014, at 17:48, Stefan Manegold < Stefan.Manegold@cwi.nl > wrote:
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.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