**SQL Apprentice Question**

wanted to create random numbers for some strange purpose using SQL

Server 2000. Even if the numbers are going to repeat its fine. [Pls note I

don't want to use newid() ].

For ex: I want to pass two integer values say, 10, 30 ... now i expect

randoms to be generated between these two numbers. How do to it in SQL

Server. Can any one help me in this?

**Celko Answers**

The first problem is that there are two kinds of random selection from

a set:

1) With replacement = you can get multiple copies of the same value.

This is shooting dice.

This one is easy if you have a random function in your SQL product.

Most of the pseudo-random generators return a floating point fraction

value between 0.00 and 0.9999.... at whatever precision your SQL engine

has. The choice of a seed to start the generator can be the system

clock or some other constantly changing value.

SELECT S1.key_col

FROM SomeTable AS S1, SomeTable AS S2

WHERE S1.key_col <= S2.key_col

GROUP BY S1.key_col

HAVING COUNT(S2.key_col)

= (SELECT COUNT(*)

FROM SomeTable AS S3) * RANDOM(seed) + 1.0;

Or you can add a column for this.

CREATE TABLE RandNbrs2

(seq_nbr INTEGER PRIMARY KEY,

randomizer FLOAT -- warning !! not standard SQL

DEFAULT (

(CASE (CAST(RAND() + 0.5 AS INTEGER) * -1)

WHEN 0.0 THEN 1.0 ELSE -1.0 END)

* (CAST(RAND() * 100000 AS INTEGER) % 10000)

* RAND())

NOT NULL);

INSERT INTO RandNbrs2 VALUES (1, DEFAULT);

INSERT INTO RandNbrs2 VALUES (2, DEFAULT);

INSERT INTO RandNbrs2 VALUES (3, DEFAULT);

INSERT INTO RandNbrs2 VALUES (4, DEFAULT);

INSERT INTO RandNbrs2 VALUES (5, DEFAULT);

INSERT INTO RandNbrs2 VALUES (6, DEFAULT);

INSERT INTO RandNbrs2 VALUES (7, DEFAULT);

INSERT INTO RandNbrs2 VALUES (8, DEFAULT);

INSERT INTO RandNbrs2 VALUES (9, DEFAULT);

INSERT INTO RandNbrs2 VALUES (10, DEFAULT);

SELECT * FROM RandNbrs2;

2) Without replacement = you can each value only once. This is dealing

playing cards.

This is trickier. I would start with a table that has the keys and a

sequentially numbered column in it:

CREATE TABLE CardDeck

(keycol

seq INTEGER NOT NULL);

INSERT INTO CardDeck (keycol, seq)

SELECT S1.keycol, COUNT(S2.keycol)

FROM SomeTable AS S1, Sometable AS S2

WHERE S1.key_col <= S2.key_col

GROUP BY S1.key_col;

Now shuffle the deck by determing a random swap pair for all the rows:

BEGIN

DECLARE i INTEGER, j INTEGER;

SET i = (SELECT COUNT(*) FROM CardDeck);

WHILE i < 0

LOOP

SET j = (SELECT COUNT(*) FROM CardDeck) * RANDOM(seed) + 1.0;

UPDATE CardDeck

SET seq = CASE WHEN seq = i THEN j

WHEN seq = j THEN i

ELSE seq END;

WHERE seq IN (i, j);

SET i = i - 1;

LOOP END;

END;

You don't really need j, but it makes the code easier to read.

Biography:

Marsaglia, G and Zaman, A. 1990. Toward a Univesal Random Number

Generator. Statistics & Probability Letters 8 (1990) 35-39.

Marsaglia, G, B. Narasimhan, and A. Zaman. 1990. A Random Number

Generator for PC's. Computer Physics Communications 60 (1990) 345-349.

Leva, Joseph L. 1992. A Fast Normal Random Number Generator. ACM

Transactions on Mathematical Software. Dec 01 1992 v 18 n 4. p 449

Leva, Joseph L. 1992. Algorithm 712: A Normal Random Number Generator.

ACM Transactions on Mathematical Software. Dec 01 1992 v 18 n 4. p 454

Bays, Carter and W.E. Sharp. 1992. Improved Random Numbers for Your

Personal

Computer or Workstation. Geobyte. Apr 01 1992 v7 n2. p 25

Hulquist, Paul F. 1991. A Good Random Number Generator for

Microcomputers.Simulation. Oct 01 1991 v57 n 4. p 258

Komo, John J. 1991. Decimal Pseudo-random Number Generator. Simulation.

Oct 01 1991 v57 n4. p 228

Chambers, W.G. and Z.D. Dai. 1991. Simple but Effective Modification to

a Multiplicative Congruential Random-number Generator. IEEE

Proceedings.Computers and Digital Technology. May 01 1991 v 138 n3. p

121

Maier, W.L. 1991.. A Fast Pseudo Random Number Generator. Dr. Dobb's

Journal.May 01 1991 v17 n 5. p 152

Sezgin, Fatin. 1990. On a Fast and Portable Uniform Quasi-random Number

Generator. Simulation Digest. Wint 1990 v 21 n 2. p 30

Macomber, James H. and Charles S. White. 1990. An n-Dimensional Uniform

Random Number Generator Suitible for IBM-Compatible Microcomputers.

Interfaces. May 01 1990 v 20 n 3. p 49

Carta, David G. 1990. Two Fast Implementations of the "Minimal

Standard" Random Number Generator. Communications of the ACM. Jan 01

1990 v 33 n 1. p 87

Elkins, T.A. 1989. A Highly Random-number Generator. Computer

Language. Dec 01 1989 v 6 n 12 p 59

Kao, Chiang. A Random Number Generator for Microcomputers. OR: The

Journal of the Operational Research Society. Jul 01 1989 v 40 n 7. p

687

Chassing, P. 1989. An Optimal Random Number Generator Zp. Statistics &

Probability Letters. Feb 01 1989 v 7 n 4. p 307

Also, you can contact Kenneth G. Hamilton 72727,177 who has done some

work with RNG's. He has implemented one (at least one) of the best.

"A Digital Dissolve for Bit-Mapped Graphics Screens" by Mike Morton in

Dr.Dobb's Journal, November 1986, page 48.

CMOS Cookbook by Don Lancaster; Sams 1977, page 318.

Art of Computer Programming, Volume 2: Seminumeral Algorithms, 2nd

edition by Donald Knuth; Addison-Wesley 1981; page 29.

Numerical Recipes in Pascal: The Art of Scientific Computing by Press

et al.; Cambridge 1989; page 233.

## No comments:

Post a Comment