On Mon, Oct 7, 2013 at 3:13 PM, Hadley Wickham <h.wickham@gmail.com> wrote:

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

Except it doesn't, because monetdb doesn't supported limit/order by in
subqueries...

The query you wrote above IS valid and does work in MonetDB, provided that Q is valid. But if you want to include order by and limit in Q, then Q isn't valid :-)

Possible solutions: remove order by/limit from Q (the schema doesn't change), or use the where clause on Q, without making it a subquery.

However, I was incorrect about the format. The default format (\f sql) will give you the schema without datatypes. To get datatypes as well you will need to set "\f raw"

>> 3) where is a good place to read the ANSI SQL standards?
>
> Easiest is to paste your exact question into the google box ;)

I have done that. But e.g. "ansi sql subquery order by" does not
return any pointers to the spec.

Actually I had assumed it is available online, but apparently if you need to order a copy at iso.org.

You can find reliable information on this matter from other sources, included www.w3schools.com, http://en.wikipedia.org/wiki/Order_by (I know, wikipedia is not so official, but it does contain references and it is reviewed, so it usually is rather accurate).

What you should not look at is vendor-specific documentation, as long as you want to know what the standard is.

Roberto

Hadley

--
Chief Scientist, RStudio
http://had.co.nz/
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list