Re: Force randomize within a set of numbers

From: Vladimir M. Zakharychev <>
Date: Thu, 3 Dec 2009 23:43:17 -0800 (PST)
Message-ID: <>

On Dec 4, 1:01 am, CrazyKarma <> 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
    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 for some reason - returns wrong results. In 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.


   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm) Received on Fri Dec 04 2009 - 01:43:17 CST

Original text of this message