[sqlite] Enhance the SELECT statement?

[Petite Abeille]

> On Aug 17, 2015, at 8:08 PM, R.Smith <rsmith at rsweb.co.za> wrote:
> 
> CORRECTION: It seems one of the two options I've mentioned earlier, namely 
> the CREATE TABLE AS SELECT... does not actually work on the back of a WITH 
> clause. The other option still do, but this request has more appeal now.

Hmmm?!?

create table foo as

with
DataSet( position )
as
(
  select  1 as position
  union all
  select  DataSet.position + 1 as position
  from    DataSet
  where   DataSet.position < 10
)
select  *
from    DataSet;

select * from foo;