Random Number Generation

[Philland]

I need to generate 3,000,000 random unique numbers, each number in a separate record.

I can set up a loop to create new records and exit loop when the record count reaches 3,000,000 based on a sequential number field.

What is the best way to generate the random/unique number though? I would prefer to do this in one pass, but if necessary is there a way to completely randomize the records then use a second sequential number field so I end up with a sequence control number and a random number?

[Kurt Knippel]

quote:

---

Originally posted by Philland:

I need to generate 3,000,000 random unique numbers, each number in a separate record.

I can set up a loop to create new records and exit loop when the record count reaches 3,000,000 based on a sequential number field.

What is the best way to generate the random/unique number though? I would prefer to do this in one pass, but if necessary is there a way to completely randomize the records then use a second sequential number field so I end up with a sequence control number and a random number?

---

There might be 3 million way to accomplish this.

I would first ask what you mean by a "random unique number". I understand that you do not want any duplicate numbers, but what do you mean by random, and what kind of number are you looking for.

With that many number, I would almost assume that a simply Random function will end up with duplicates, perhaps many.

You could test each number via validation or with a self-relationship and if the test is true, then generate a new number and test again, repeat until the test is false.

If you are simply looking for a single pass setup of numbers that are non-sequential and non-user entered you could use some combination of the date, time and recordID. All of which are numbers and could be combined together into a number that looks pretty random.

[Philland]

Thanks for the quick response. Ideally I would like to randomly pull numbers from a specified range. The numbers are to be used in a prize drawing. I can choose the range. Any ideas?

[LiveOak]

Setting a range is really not a problem. This can be done by multiplying and/or offsetting FM's random number generator which generates numbers from 0 to 1. for example, if you wanted numbers from 0 to 7, you would multiply the FM generate random number by 7. The harder part is making 3,000,000 random numbers unique and not skewing the distribution. Making part of the random number from times, dates, etc. can make the distribution uneven between the end points. If just depends upon how critical this is.

-bd

[Vaughan]

Try this: create an unstored field that generates a random number (just a plain old random calc will do) and don't worry if some are not unique. Now sort the database on this random field. While sorted, use the replace command to generate a serial number in another field, then unsort the database. Guaranteed unique and random, and contiguous to boot!

The most minimalist approach might be to script the process and use a single field to do the work: first replace the field with a calculated random number, sort on the random number then replace the field with a serial number and unsort.