Sounds like there are 32768 "steps" between zero and one in the floating
point number returned by RAND().
Instead of:
RAND()*999999999
try this:
INT(RAND()*9999)*100000 + INT(RAND()*99999)
That will generate a 9-digit number by combining a 4-digit random number
with a 5-digit random number.
A quick check for 999,618 records produced 998,487 unique values. The same
check using RAND()*999999999 produced 32,768 unique values.
Have fun!
Richard
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mark Murphy/STAR BASE
Consulting Inc.
Sent: Tuesday, March 15, 2011 4:02 PM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL RAND() Function
This might be of interest to some of you, I wanted generate 9 digit random
numbers to sort a file in a random order. Turns out that though you can
indeed generate a 9 digit random number, RAND() will only provide 32768
unique values. I expected more given that it returns a double precision
float, but 32768 is all you get.
Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/midrange-l.
****************************************************************************
********
This footnote confirms that this email message has been scanned by
PineApp Mail-SeCure for the presence of malicious code, vandals & computer
viruses.
****************************************************************************
********
_____
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1204 / Virus Database: 1498/3508 - Release Date: 03/15/11
midrange.com
