Re: ORDER BY in sub-query

Thank you Roberto, I hadn't realised that was the case, I am used to PostgreSQL's behaviour. Thanks for the alternative. On 5 October 2013 13:34, Roberto Cornacchia wrote:

.. and then of course w=you can use that within your outer query, joining with orders.

On Sat, Oct 5, 2013 at 2:32 PM, Roberto Cornacchia < roberto.cornacchia@gmail.com> wrote:

...

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

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list