Re: Force randomize within a set of numbers
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