Thanks, will look at that as well. I was thinking achieving this with a user function, maybe using sqlite3_randomness, but I this looks very good.
RBS On Fri, Jan 22, 2016 at 12:39 PM, Stephan Beal <sgbeal at googlemail.com> wrote: > On Fri, Jan 22, 2016 at 12:22 PM, Stephan Beal <sgbeal at googlemail.com> > wrote: > > > > > > > On Fri, Jan 22, 2016 at 12:11 PM, Bart Smissaert < > bart.smissaert at gmail.com > > > wrote: > > > >> Say I want 10000 random numbers between 100 and 1000 how can I do that > >> without > >> selecting from a table? > >> I know I can do: > >> select abs(random() %(1000 - 100)) + 100 as rnd from TableWith10000Rows > >> but there must be a better way. > >> > >> > > Maybe not perfect, but this seems to work... > > > > sqlite> with conf(max) as (select 10), rnd(n, x) as (select abs(random() > > %(1000 - 100)) + 100, 1 union all select abs(random() %(1000 - 100)) + > 100, > > x+1 from rnd where x<(select max from conf)) select * from rnd; > > > > Another variant which moves all the configurable bits up one level: > > sqlite> with conf(max,slack) as (select 20, 2), rnd(n, x) as (select > abs(random() %(max - slack)) + slack, 1 from conf union all select > abs(random() %(max - slack)) + slack, x+1 from rnd, conf where x<conf.max) > select * from rnd; > 6|1 > 3|2 > 10|3 > 18|4 > 12|5 > 5|6 > 2|7 > 2|8 > 11|9 > 18|10 > 16|11 > 7|12 > 9|13 > 13|14 > 10|15 > 3|16 > 17|17 > 2|18 > 17|19 > 19|20 > > To get the range from your original description, swap out max/slack with > 1000/100, but if i'm not mistaken it will returns the range inclusive range > [0,999]. > > It also works as a view: > > sqlite> create view rng10 as with conf(max,slack) as (select 10, 0), rnd(n, > x) as (select abs(random() %(max - slack)) + slack, 1 from conf union all > select abs(random() %(max - slack)) + slack, x+1 from rnd, conf where > x<conf.max) select * from rnd; > sqlite> select * from rng10; > 5|1 > 0|2 > 3|3 > 2|4 > 4|5 > 8|6 > 3|7 > 1|8 > 1|9 > 1|10 > sqlite> select * from rng10; > 6|1 > 5|2 > 0|3 > 1|4 > 5|5 > 3|6 > 3|7 > 4|8 > 5|9 > 1|10 > > -- > ----- stephan beal > http://wanderinghorse.net/home/stephan/ > http://gplus.to/sgbeal > "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of > those who insist on a perfect world, freedom will have to do." -- Bigby > Wolf > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
