On 5 Nov 2014 18:49, "Stefan Manegold" <Stefan.Manegold@cwi.nl> wrote:

passing table identifiers IMHO clashes with SQL type system.

passing table-typed arguments might work --- you still need one function per table signature, but no longer per table name;
IMHO, there are plans to work on supporting something like that ...

Stefan

----- Original Message -----
>
>
> 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 ;)
> On 5 Nov 2014 17:24, "Martin Kersten" < Martin.Kersten@cwi.nl > wrote:
>
>
> 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
>
> _______________________________________________
> 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