Re: Force randomize within a set of numbers

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Thu, 3 Dec 2009 23:43:17 -0800 (PST)
Message-ID: <14695f73-53cc-45c8-bfc6-c2a54c398081_at_k17g2000yqh.googlegroups.com>

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 Received on Fri Dec 04 2009 - 01:43:17 CST