Re: Selecting a random row from a table

In Oracle8, you can use the built-in DBMS_RANDOM package to generate random numbers.

                                  Rick

GHouck <hksys_at_teleport.com> writes:

> Paul Davies wrote:
> >
> > Well since no one has replied here's my suggestion - which I find rather
> > messy:
> >
> > Use a cursor to select the data into a vararray
> >
> > Get a random number between 1 and count(*) - using the rand_int function
> > from technet.
> >
> > Get the value of the vararray with an index equal to that of the random
> > number
> >
> > Anyone got any better suggestions??
> >
> > Paul Davies wrote in message <375939d2_at_newsread3.dircon.co.uk>...
> > >I'm wish a select a single row randomly from a table.
> > >
> > >Can someone tell me how to do this using PL/SQL?
> > >
> > >Thanks
> > >
> > >Paul
>
> I found this a while ago. It is a package:
>
> The package..
>
>
> package random is
> /* Linear congruential random number generator */
>
> /* Returns random integer between [0, r-1] */
> function rndint(r in number) return number;
>
> /* Returns random real between [0, 1] */
> function rndflt return number;
>
> end; /* package random */
>
> package body random is
> /* Linear congruential random number generator */
>
> m constant number:=100000000; /* initial conditions */
> m1 constant number:=10000; /* (for best results) */
> b constant number:=31415821; /* */
>
> a number; /* seed */
>
> the_date date; /* */
> days number; /* for generating initial seed */
> secs number; /* */
>
> /*-------------------------- mult ---------------------------*/
> /* Private utility function */
>
> function mult(p in number, q in number) return number is
> p1 number;
> p0 number;
> q1 number;
> q0 number;
> begin
> p1:=trunc(p/m1);
> p0:=mod(p,m1);
> q1:=trunc(q/m1);
> q0:=mod(q,m1);
> return(mod((mod(p0*q1+p1*q0,m1)*m1+p0*q0),m));
> end; /* mult */
>
> /*-------------------------- rndint --------------------------*/
> /* Returns random integer between [0, r-1] */
>
> function rndint (r in number) return number is
> begin
> /* generate a random number and set it to be the new seed */
> a:=mod(mult(a,b)+1,m);
>
> /* convert it to integer between [0, r-1] and return it */
> return(trunc((trunc(a/m1)*r)/m1));
> end; /* rndint */
>
> /*-------------------------- rndflt --------------------------*/
> /* Returns random real between [0, 1] */
>
> function rndflt return number is
> begin
> /* generate a random number and set it to be the new seed */
> a:=mod(mult(a,b)+1,m);
>
> /* return it */
> return(a/m);
> end; /* rndflt */
>
>
> begin /* package body random */
> /* Generate an initial seed "a" based on system date */
> /* (Must be connected to database.) */
> the_date:=sysdate;
> days:=to_number(to_char(the_date, 'J'));
> secs:=to_number(to_char(the_date, 'SSSSS'));
> a:=days*24*3600+secs;
> end; /* package body random */
>
>
> Dennis M. Hancy wrote:
>
> > Is there a function in SQL to produce random numbers?
> >
> > Dennis Hancy
> > Eaton Corporation
> > Cleveland, OH
>
>
>
> I take no credit or blame.
>
> Yours,
>
> Geoff Houck
> systems hk
> hksys_at_teleport.com
> http://www.teleport.com/~hksys

--

                                        Rick
                                        Rick Wessman
                                        Security and Directory Technologies
                                        Server Technologies
                                        Oracle Corporation
                                        rwessman_at_us.oracle.com