Re: SQL RAND function question

[Adam Glauser <adamglauser@xxxxxxxxxxxx>]

Wilt, Charles wrote:
> IIRC every example I've seen about using _any_ built in random number function has
> said basically, they return [0,1) or [0,1] and to get a number between a given range multiply by the
> range.  (IBM's SQL manual included!)

I couldn't tell from the SQL manual whether it was [0,1] or [0,1).  It 
can make rather a big difference, as we've seen, and it would be nice to 
have it say explicitly.

> So what is the "right" answer to using such a built in random number function?

Well, if the range of the RAND() function is [0,1), then I guess
    floor( rand() * (n+1) )
would give an integer 0 <= x <= n.  As Michael mentioned, if you want 
the range to be 1 <= x <= n, then I think
    floor(rand() * n) + 1
would work.

If the range of RAND() is [0,1], then I think that your solution to use
floor( rand() * (n+0.99999) )
would work well.  My gut feeling is that it might throw off the 
distribution at the top end a touch, but I can't really back that up 
mathematically right now.