Hi Pete,
Hannes is correct. The query you posted here overloads the limit and the order by clauses to realise a topn operation.
Although many SQL vendors decided to implement such extensions, they are not compliant to the standards, for which both limit and order by are defined as affecting the *presentation* of results and not necessarily their computation. Conceptually, you always have to think that they compute the whole result, and then present a (sorted) portion to the user, regardless of how the query can then be optimised. That's why they are not defined for subqueries.

A way to obtain what you want is:

with sub as (select salesman_id, row_number() over(order by order_count desc) as r from order_counts)
select salesman_id from sub
where r <= 5

Cheres,

Roberto

On 4 Oct 2013 17:08, "Pete Hollobon" <hollobon@gmail.com> wrote:

Hi Hannes,

That's not always the case; how about something like getting the names of all order items sold by the top 5 salesmen by order count, using a query like this:

select item_name
from orders
where salesman_id in (
select salesman_id
from order_counts
order by order_count desc
limit 5)

Or do you mean that it's a non-standard SQL extension to allow order by / limit in subqueries?

thanks,
Pete.

On 4 October 2013 15:33, Hannes Mühleisen <Hannes.Muehleisen@cwi.nl> wrote:
Hello Hadley and list,

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.

Best,

Hannes

On 10/04/2013 04:22 PM, Hadley Wickham wrote:

Currently, monetdb fails on this query:

SELECT * FROM (SELECT *
FROM "Batting"
ORDER BY "playerID")

with

syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION
or ')' in: "select * from (select *

(obviously that query isn't very useful, but it makes it harder to
automatically generate sql)

In case it matters, that query (with minor escaping variations) works
in sqlite, postgres and mysql.

Hadley

_______________________________________________
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