Re: Force randomize within a set of numbers

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Fri, 4 Dec 2009 12:05:52 -0800 (PST)
Message-ID: <37f0c000-6afe-45bb-8c71-fe7fe5b1a26a_at_r5g2000yqb.googlegroups.com>



On Dec 3, 5:01 pm, 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

One more, which was originally based on my second solution, this time ordering the rows in random order:
SELECT
  ROWNUM ITEM_NUMBER,
  REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) ITEM FROM
  (SELECT
    '2,8,6,5,54,100,67,7778,6' ITEMS
  FROM
    DUAL)
CONNECT BY
  REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) IS NOT NULL ORDER BY
  DBMS_RANDOM.VALUE(0,1); ITEM_NUMBER ITEM
----------- ----

          8 7778
          4 5
          9 6
          6 100
          5 54
          2 8
          7 67
          3 6
          1 2

Now, just slide the above into an inline view and retrieve just the first row:
SELECT
  ITEM_NUMBER,
  ITEM
FROM
(SELECT
  ROWNUM ITEM_NUMBER,
  REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) ITEM FROM
  (SELECT
    '2,8,6,5,54,100,67,7778,6' ITEMS
  FROM
    DUAL)
CONNECT BY
  REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) IS NOT NULL ORDER BY
  DBMS_RANDOM.VALUE(0,1))
WHERE
  ROWNUM=1; ITEM_NUMBER ITEM
----------- ----

          6 100

There are probably a couple more ways to pick a random element.

Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Dec 04 2009 - 14:05:52 CST

Original text of this message