Re: Can't use ORDER BY in subqueries

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 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