Hi folks, I continue searching for a workaround for "create table .. as select .. order by .. limit .." in MonetDB. I would like to create a table using a result-set of a query with "order by & limit" though I'm aware that this is a geeky requirement. -- pareparing data create table t1 (f1 int, f2 char); insert into t1 values(3, 'a'); insert into t1 values(1, 'b'); insert into t1 values(4, 'c'); insert into t1 values(5, 'd'); insert into t1 values(2, 'e'); [Not allowed in SQL spec but what I somehow want] create table t2 as select * from t1 where f2 <> 'b' order by f1 limit 3; Here below is my unwise workaround representing the above DDL&DML :-( Are there any better suggestions? ---------------------------- [workaround #1] create table t2 as ( select f1,f2 from (select *, row_number() over (order by f1) as rownum from t1 where f2 <> 'b') as tmp where tmp.rownum <= 3 ) with data; Pros: Everything is done in SQL. Cons: However, more than 3 or every records are scanned. Automatic query rewriting is slightly hard. ---------------------------- [workaround #2] create table t2 as select * from t1 where f2 <> 'b' with no data; Then, is t1 fully scanned in the above case? Or, only 1 record is scanned with the "no data" option. select * from t1 where f2 <> 'b' order by f1 limit 3; foreach(r: results) { // batch inserts using prepared statements insert into t2 values(r.f1,..,r.fN); insert into t2 values(r.f1,..,r.fN); insert into t2 values(r.f1,..,r.fN); insert into t2 values(r.f1,..,r.fN); } Pros: Redundant scan might be avoided. Cons: Data copy between client and DB would happen. Moreover, iterating the resultset and inserting records would be in-efficient. --------------------------------------- BTW, a table 't_union' is not created though the following query as in [1] are successfully executed. create table t1 (f1 int); -- insert into t1 values(1); /* at least one result is requred */ create table t2 (f1 int); create table t_union as (select f1 from t1 union all select f1 from t2) with data; The table 't_union' is not created even when using "with no data" option. This would be a minor documentation issue. [1] http://old.nabble.com/'create-table-as-select...'-from-union-queries-doesn't-work-td26517185.html#a26517185 Thanks, Makoto