Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-13-2008
07:13 PM

03-13-2008
07:13 PM

Is there a way to generate a unique random number? It tried RANDOM(lower limit, upper limit) and it generated random numbers however there were repeats. I need a unique number for each row.

Thanks

Thanks

4 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-14-2008
11:22 AM

03-14-2008
11:22 AM

Could you use a table to incr a counter and use that for each row? It wouldn't be random; however, it would provide for your uniqueness requirement.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-14-2008
11:26 AM

03-14-2008
11:26 AM

You may have a larger range between lower and upper limits to create almost unique values ( generally 10 times of the rows). RANDOM function is activated for each row processed so you cannot guarantee unique values using random function.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-20-2008
06:04 AM

03-20-2008
06:04 AM

Hi,

You can create Identity columns to make use of system generated uniqueness value.

Another simple way to generate unique number without specifying the upper limit is by using CSUM.

Eg:-

CSUM(1, sort_col)

Regards,

Balamurugan

You can create Identity columns to make use of system generated uniqueness value.

Another simple way to generate unique number without specifying the upper limit is by using CSUM.

Eg:-

CSUM(1, sort_col)

Regards,

Balamurugan

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-24-2008
02:44 PM

03-24-2008
02:44 PM

The generator shown here is an implementation of the additive congruential method of generating values in pseudo-random order and is due to Roy Hann of Rational Commerce Limited, a CA-Ingres consulting

firm. It is based on a shift-register and an XOR-gate, and it has its origins in cryptography. While there are other ways to do this, this code is nice because:

1) The algorithm can be written in C or another low level language for speed. But math is fairly simple even in base ten.

2) The algorithm tends to generate successive values that are (usually) "far apart", which is handy for improving the performance of tree indexes. You will tend to put data on separate physical data pages in storage.

3) The algorithm does not cycle until it has generated every possible value, so we don't have to worry about duplicates. Just count how many calls have been made to the generator.

4) The algorithm produces uniformly distributed values, which is a nice mathematical property to have. It also does not include zero.

It might not be obvious that successive values are far apart when we are looking at a tiny 4-bit register. But it is clear that the values are generated in no obvious order, all possible values except 0 are eventually produced, and the termination condition is clear -- the Generator cycles back to 1.

Generalizing the algorithm to arbitrary binary word sizes, and therefore longer number sequences, is not as easy as you might think. Finding the "tap" positions where bits are extracted for feedback varies according to the word-size in an extremely non-obvious way. Choosing incorrect tap positions results in an incomplete and usually very short cycle, which is unusable. If you want the details and tap positions for words of one to 100 bits, see E. J. Watson, "Primitive Polynomials (Mod 2)", Mathematics of Computation, v.16, 1962, p.368-369.

UPDATE Generator31

SET keyval =

keyval/2 + MOD(MOD(keyval, 2) + MOD(keyval/8, 2), 2)*2^30;

Or if you prefer, the algorithm in C:

int Generator31 ()

{static int n = 1;

n = n >> 1 | ((n^n >> 3) & 1) << 30;

return n;