Martin,
Your answer to this question is useful but triggers a feature request that I'm very interested in for long :)
The major drawback of your workaround is that the name of the table to be limited is hard-coded inside the function. Which makes this solution impractical for real scenarios.
What is much missed - and would make your solution very useful in practice - is to be able to pass table identifiers as function parameters.
I could produce a long list of issues that would be solved by this ;)
Hi
This recurring question has a simple solution in MonetDB for most
of the cases considered. Simple rip out the subquery and encapsulate
it in table producing function. The following example shows how:
-- select * from tables where id in (select id from tables limit 1);
create function limited()
returns table (id integer)
begin
return select id from tables order by id desc limit 1;
end;
select * from tables where id in (select * from limited());
regards, Martin
On 05/11/14 16:52, Martin Kersten wrote:
Hi_______________________________________________
A possible workaround is to enclose the query in a table producing
function.
regards, Martin
On 05/11/14 16:49, Leo Kotschenreuther wrote:
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
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