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
