On 5 Oct 2013 15:24, "Hadley Wickham" <h.wickham@gmail.com> 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?

Easiest is to paste your exact question into the google box ;)

> 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 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
>>>
>
>
> --
> Chief Scientist, RStudio
> http://had.co.nz/
>
> _______________________________________________
> users-list mailing list
> users-list@monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list
>