Re: Another use of SQL RAND

the rand sql function can be used to shuffle numbers.
Suppose that you want to randomly shuffle the numbers from 1..100 .

First, create a table with an automatic identity row. In our example, the
identity row saves us the effort of creating the population to be shuffled.
A sequence can be used instead, or you can fill it up programatically.

CREATE TABLE shuffle (nid SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY
(START WITH 1 INCREMENT BY 1 CYCLE),
rnd DECIMAL (10 , 10) )

Now, execute the following 100 times
INSERT INTO shuffle (rnd) VALUES(RAND())

We have created a table with a hundred rows numbered 1 to 100, and each row
contains a random number.

The following SQL will display the shuffled sequence.

SELECT nid FROM shuffle ORDER BY rnd


Shalom Carmel
-----------------------
www.venera.com