## Rand() function error

 Author Message db042188 SSC-Addicted Group: General Forum Members Points: 415 Visits: 69 Thanks Jeff.That's my point. If something can generate both a negative and positive number 2, they may look random but abs reverses that effect.Also, it seems to us that RAND is doing fine between rows since the seed being passed as proposed is changing. Matt Miller (4) SSC Guru Group: General Forum Members Points: 68094 Visits: 19995 stan.teitelbaum (2/15/2010)Thanks Jeff.That's my point. If something can generate both a negative and positive number 2, they may look random but abs reverses that effect.Also, it seems to us that RAND is doing fine between rows since the seed being passed as proposed is changing.I've actually used both. If you think about it, using RAND(Checksum(NewID())) will only generate as many unique values as Checksum(NewID()) in any given run since Checksum(NewID()) is what generates the "unique seed". So it comes down to what kind of numbers do you need? Is it better to work from a float and muliply it out, or start with a big number and make it smaller to fit your actual allowable range. Since they're both basically the same, I tned to work with what is fastest to generate (i.e. Jeff's method listed above).By the way - RAND() isn't random at all. For example, try running this:`select rand(1)goselect rand(1)goselect rand(1)goselect rand(1)goselect rand(1)goselect rand(1)`In other words - it generates a predictable result for the same seed, even on separate runs and on different sessions. The only thing that makes it "seem" random is that it will "pick" a seed during different runs if you don't pass it a seed.It also apparently has something that only uses positive seeds , since RAND(1) = RAND(-1)So again, you really don't get anything other that somewhat slower perf out of using RAND(). ----------------------------------------------------------------------------------Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? db042188 SSC-Addicted Group: General Forum Members Points: 415 Visits: 69 thanks for clarifying Matt. goran.biljetina SSC Rookie Group: General Forum Members Points: 45 Visits: 50 Thought I'd share ... use it as you see fit.I wrote this for something else I need to poc, etc.Step 1:`CREATE VIEW dbo.vRandASSELECT RAND() as [RANDOM]`Step 2:`USE SomeDBGO/* =========================================================================================================================Author: Goran BiljetinaCreate date: 2012-11-13Description: Random number generator between 1 and base^powerLIMITS: Returns positive integers, base and exponent inputs are integers as well (that can be modified as needed)========================================================================================================================= */CREATE FUNCTION dbo.fnRandNumber( @base bigint = 10 --> base number, 10 by default ,@exp int --> exponent value, e.g. max 18 when base = 10)RETURNS bigintASBEGIN------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------declare @r int, @ef int, @i intset @r= @exp+1 --> range or maximum sequence number, how many multiplication loops?set @ef= 0 --> initial multiplication factorset @i= 1 --> increment of multiplication factor and decrement of range or sequencedeclare @Power10 table (Number bigint, Sequence int); --> generate sequence buckets based on base number and power value input begin while @r >= 0 begin insert into @Power10 (Number,Sequence) select power(@base,@ef),(@ef) set @r = (@r - @i) set @ef = (@ef + @i) if @r <= 0 BREAK else CONTINUE end end--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------> GENERATE RANDOM NUMBER FROM 1 to base^power (max exp=18 when base=10, 8 byte int limit)declare @rspan bigint, @rnumber bigint, @rseqmax int, @rseq int, @rseed bigintset @rseqmax = @exp --> get sequence or power limitset @rseq = (FLOOR(((select * from vRand)*@rseqmax))+1) --> get random sequence w/in limitset @rseed = (select number from @Power10 where Sequence = @rseq) --> get number seed w/in sequenceset @rspan = (FLOOR((((select * from vRand))*((select * from vRand)*@rseed)))+1) --> get rand span w/in rand number seedset @rnumber = FLOOR((select * from vRand)*@rspan)+1 --> get rand number w/in spanRETURN(@rnumber);ENDGO`Step 3.`--> SAMPLE SETS:select dbo.fnRandNumber(10,3)[(10,3)];select dbo.fnRandNumber(10,5)[(10,5)];select dbo.fnRandNumber(10,9)[(10,9)]; --> max for base 10 (if return 8 byte signed integer)select dbo.fnRandNumber(10,18)[(10,18)];select dbo.fnRandNumber(2,8)[(2,8)];select dbo.fnRandNumber(2,16)[(2,16)];select dbo.fnRandNumber(2,32)[(2,32)]; --> max for base 2 (if return 8 byte signed integer)select dbo.fnRandNumber(2,62)[(2,62)];`And this, basically the same thing in JS:`// get random number within provided base + exponent// by Goran Biljetina// code as is function isEmpty(value){ return (typeof value === "undefined" || value === null);}var numSeq = new Array();function add(num,seq){ var toAdd = new Object(); toAdd.num = num; toAdd.seq = seq; numSeq[numSeq.length] = toAdd;}function fillNumSeq (num,seq){ var n; for(i=0;i<=seq;i++){ n = Math.pow(num,i); add(n,i); }}function getRandNum(base,exp){ if (isEmpty(base)){ console.log("Specify value for base parameter"); } if (isEmpty(exp)){ console.log("Specify value for exponent parameter"); } fillNumSeq(base,exp); var emax; var eseq; var nseed; var nspan; emax = (numSeq.length); eseq = Math.floor(Math.random()*emax)+1; nseed = numSeq[eseq].num; nspan = Math.floor((Math.random())*(Math.random()*nseed))+1; return Math.floor(Math.random()*nspan)+1;}console.log(getRandNum(10,20),numSeq);//getRandNum(-10,20);//console.log(getRandNum(-10,20),numSeq);//console.log(numSeq);` starshayiz Old Hand Group: General Forum Members Points: 347 Visits: 0 Thank you for your posts starshayiz Old Hand Group: General Forum Members Points: 347 Visits: 0 thank you