Re: ORDER BY in sub-query

On 5 Oct 2013 15:24, "Hadley Wickham" wrote:

Hi Roberto,

A couple of follow up questions:

1) is row_number() documented somewhere? What other window functions does

monetdb implement?

If I'm not mistaken, the online documentation doesn't actually include these functions yet. MonetDB supports the standard syntax, that's easy to find, just google for it.

2) in other SQL databases you can often efficiently find out the column types of a query Q by running SELECT * FROM (Q) WHERE 0=1 is there a similar idiom for monetdb?

This is a valid empty query that will show the header (when the standard result format is selected), with data types, and no tuple. So yes, I guess this would give you what you want.

3) where is a good place to read the ANSI SQL standards?

Hadley

On Saturday, October 5, 2013, Roberto Cornacchia 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

...

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

Easiest is to paste your exact question into the google box ;) 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. 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

-- Chief Scientist, RStudio http://had.co.nz/

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