Home » SQL & PL/SQL » SQL & PL/SQL » Random value
Random value [message #242657] Mon, 04 June 2007 10:17 Go to next message
j0zele
Messages: 15
Registered: December 2006
Junior Member
Hi,

i want to take a random value in a column, is this posible, any random function in oracle 9.2??

Thanks Smile

Re: Random value [message #242659 is a reply to message #242657] Mon, 04 June 2007 10:29 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
lookup the DBMS_RANDOM function. For example, you can use DBMS_RANDOM as:

system@DBASE-SQL>>select dbms_random.random, scott.emp.* from scott.emp;

    RANDOM      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
-548765830       7369 SMITH      CLERK           7902 17-DEC-80        800                    20
 189653107       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
-1.018E+09       7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
1213871144       7566 JONES      MANAGER         7839 02-APR-81       2975                    20
1159163465       7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
1971174924       7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
1866967630       7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
-1.760E+09       7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
1988131717       7839 KING       PRESIDENT            17-NOV-81       5000                    10
-126118855       7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
 148962374       7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
1410177638       7900 JAMES      CLERK           7698 03-DEC-81        950                    30
-628557575       7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
-626346820       7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

system@DBASE-SQL>>

Re: Random value [message #242660 is a reply to message #242657] Mon, 04 June 2007 10:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did a google for Oracle random give you the same 1,360,000 hits as it did me?
Re: Random value [message #242667 is a reply to message #242657] Mon, 04 June 2007 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from (select ename from emp order by dbms_random.value) where rownum=1;
ENAME
----------
FORD

1 row selected.

SQL> /
ENAME
----------
ALLEN

1 row selected.

SQL> /
ENAME
----------
KING

1 row selected.

SQL> /
ENAME
----------
JONES

1 row selected.

Regards
Michel
Re: Random value [message #242676 is a reply to message #242667] Mon, 04 June 2007 11:37 Go to previous messageGo to next message
j0zele
Messages: 15
Registered: December 2006
Junior Member
Thanks a lot, it works

P.D The problem cause i didnt use google with the random word is cause im spanish and i dont think in words like "random", in spanish is "aleatorio" to search in google, i write in english when i need to ask something here
If you search --aleatorio oracle-- in google...

[Updated on: Mon, 04 June 2007 11:43]

Report message to a moderator

Re: Random value [message #242691 is a reply to message #242676] Mon, 04 June 2007 12:43 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, I'm Croatian and when I don't know how to create a view in Oracle I don't Google for "kreiraj pogled" but "create view".

I spent some time thinking about your explanation and still can't figure out why would you want to Google for "aleatorio" ... unless your Oracle speaks Spanish. Also, how did you find this forum? Do you (in Spain) also call it "Frequently Asked Questions"? I don't think so.

Finally, there's OraFAQ Forum for Spanish-speaking Oracle users. Perhaps you'd want to visit it.
Re: Random value [message #242741 is a reply to message #242691] Mon, 04 June 2007 23:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
There's nothing wrong with your english, so I'd stick to the main forums. The spanish forum is visited much less frequently than the main ones.
Just a word of well-meant advice: if you get used to using Google, you'd be amazed of the amount of Oracle-related stuff you can find. Simply try out some keywords, always in combination with "oracle".
Re: Random value [message #272522 is a reply to message #242667] Fri, 05 October 2007 07:07 Go to previous messageGo to next message
marwi
Messages: 7
Registered: July 2007
Junior Member
If one is still running on an old Oracle 8.0.4, is there any kind of tip, you could pass along, that would produce x random rows from a table?

dbms_random is not available and neither is the sample(x) extension to the select syntax.
Re: Random value [message #272523 is a reply to message #272522] Fri, 05 October 2007 07:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
don't add an order by.
That will get you a random set of rows. (well, kind of, but there is no such thing as a really random set).
The set will unfortunately be the same many many times you query..

What's the business need for a random set of rows?
Re: Random value [message #272525 is a reply to message #272523] Fri, 05 October 2007 07:18 Go to previous messageGo to next message
marwi
Messages: 7
Registered: July 2007
Junior Member
If I omit the order by column, the order of the selected rows will (almost always) be the order the rows were created.

The business need could be picking 10 winners in a competition among several thousand contestants. It will be quite obvious if it is always the first ten to enter the contest that win. (Given that the contestants can see who entered the competition)

Re: Random value [message #272529 is a reply to message #272522] Fri, 05 October 2007 07:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use one of the numerous random algorithms you can find on Internet and code it in PL/SQL.

Regards
Michel
Re: Random value [message #272530 is a reply to message #272525] Fri, 05 October 2007 07:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could do something like take the seconds part of the time the records were created and take all of the records that match the seconds part of the current time.
ie
to_char(create_Date,'ss') = to_char(sysdate,'ss')
icon7.gif  Re: Random value [message #272531 is a reply to message #272530] Fri, 05 October 2007 07:30 Go to previous messageGo to next message
marwi
Messages: 7
Registered: July 2007
Junior Member
I like your suggestion, JRowbottom! Thanks a lot!

Re: Random value [message #272535 is a reply to message #272531] Fri, 05 October 2007 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
10000/60 = 167

You will likely have a problem to find 10 random among these (about) 167.

Regards
Michel
Re: Random value [message #272537 is a reply to message #272535] Fri, 05 October 2007 08:03 Go to previous messageGo to next message
marwi
Messages: 7
Registered: July 2007
Junior Member
Well, you are right, but when no real random mechanisms are available, you have to make do with a fake solution.

And you could just limit the number to 10 by a simple rownum limitation.

I was just wondering if you could do some kind of hashing of other columns/values/time that could produce a more true random selection?
Re: Random value [message #272543 is a reply to message #272525] Fri, 05 October 2007 08:29 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This gives what appears to be a reasonably random number at most once per second:
select mod(reverse(to_char((60 * 60 * 24 * (sysdate - to_date('1','J'))))), 1500450271) / 1500450271 rand
from dual

The REVERSE function is undocumented, but I suspect Oracle implemented it for use in reverse-key indexes; therefore it should be available in v8.0

You can generate a bunch of numbers with a small change:
select mod(reverse(to_char(rownum+(60 * 60 * 24 * (sysdate - to_date('1','J'))))), 1500450271) / 1500450271 rand
from user_tables

.815718061875040975616578764975277211303 
.46237868952339307485119578481518365496 
.307099103453059391663104308240016306412 
.953759731101411490897721328079922750069 
.600420358749763590132338347919829193726 
.247080986398115689366955367759735637383 
.89374161404646778860157238759964208104 
.540402241694819887836189407439548524698 
.187062869343171987070806427279454968355 
.833723496991524086305423447119361412012 
.480384124639876185540040466959267855669 
.127044752288228284774657486799174299326 
.971765166217894601586566010224006950778 
.618425793866246700821183030063913394435 
.265086421514598800055800049903819838092 
.911747049162950899290417069743726281749 
.558407676811302998525034089583632725406 
.205068304459655097759651109423539169063 
.85172893210800719699426812926344561272 
.498389559756359296228885149103352056378 
.145050187404711395463502168943258500035 
.791710815053063494698119188783164943692 
.636431228982729811510027712207997595143 
.2830918566310819107446447320479040388 
.929752484279434009979261751887810482458 
.576413111927786109213878771727716926115 
.223073739576138208448495791567623369772 
.869734367224490307683112811407529813429 
.516394994872842406917729831247436257086 
.163055622521194506152346851087342700743


Although this gives the appearance of randomness - no apparent pattern and evenly distributed between 0 and 1 - it is not truly random. The numbers produced are entirely deterministic based on the time of day generated.

Be very careful how you use this.

You can mitigate it a little by adding a second factor: the session ID:
select 
mod(
  reverse(
    to_char(
      userenv('SESSIONID') + rownum + (60 * 60 * 24 * (sysdate - to_date('1','J')))
    )
  )
, 1500450271
) / 1500450271 rand
from user_tables


Ross Leishman
Re: Random value [message #272547 is a reply to message #272535] Fri, 05 October 2007 08:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just pick the first 10, ordered by something else unpredictable by the people making the submission.
Re: Random value [message #272550 is a reply to message #272543] Fri, 05 October 2007 08:43 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
REVERSE function existed at least since version 5.

Regards
Michel
Previous Topic: Schema object named using Oracle Function
Next Topic: How to get Package.Procedure's Parameter information?
Goto Forum:
  


Current Time: Wed Dec 07 20:17:25 CST 2016

Total time taken to generate the page: 0.09393 seconds