# Re: Force randomize within a set of numbers

From: Mark D Powell <Mark.Powell2_at_hp.com>

Date: Fri, 4 Dec 2009 06:47:09 -0800 (PST)

Message-ID: <a469fdef-428c-4dfa-b836-dca406787fd9_at_g26g2000yqe.googlegroups.com>

On Dec 4, 2:43 am, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:

Date: Fri, 4 Dec 2009 06:47:09 -0800 (PST)

Message-ID: <a469fdef-428c-4dfa-b836-dca406787fd9_at_g26g2000yqe.googlegroups.com>

On Dec 4, 2:43 am, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:

*> On Dec 4, 1:01 am, CrazyKarma <ska..._at_gmail.com> wrote:**>**> > Here is the last one for this week..I guess :)**>**> > Is there way to force the randomize within a set of number in Oracle?**> > Say I have a set of integers ( 2,8,6,5)**> > and I want to force randomize function to randomly pick within that**> > set only.**>**> > I know about the DBMS_RANDOM package, sample and seed clause but none**> > help what I need to do here.**>**> > any thoughts?**>**> > CK**>**> Straightforward way: just generate random numbers between 0 and 1,**> divide the 0..1 range into 4 subranges and then pick a number from**> your list depending on which subrange your generated random value**> belongs, like this:**>**> with rnd as (select dbms_random.value val from dual)**> select**> case**> when val < 0.25 then 2**> when val >= 0.25 and val < 0.5 then 8**> when val >= 0.5 and val < 0.75 then 6**> when val >= 0.75 then 5**> end x**> from rnd**>**> (note that this query does not work correctly in 9.2.0.8 for some**> reason - returns wrong results. In 10.2.0.4 it returns expected**> results.) Obviously, you can partition 0..1 range into as many**> subranges as there are numbers in your list and apply the same**> technique. A function that will do this automatically given an array**> of possible return values is not too hard to write.**>**> Regards,**> Vladimir M. Zakharychev**> N-Networks, makers of Dynamic PSP(tm)**> http://www.dynamicpsp.com*CK, instead of using a fixed range as Vladimir had in his example I was thinking I might use a MOD division of the random number returned from dbms_randon to generate the index key into my table of values. This would make the code flexible for variable length lists. If you have a fixed number of entries then Vlad's solution is simple and easy to understand. If your number of variables varies then I think this would fit the bill.

HTH -- Mark D Powell -- Received on Fri Dec 04 2009 - 08:47:09 CST