Re: Selecting a random row from a table

From: GHouck <hksys_at_teleport.com>
Date: Mon, 07 Jun 1999 14:13:33 -0700
Message-ID: <375C35FD.6799@teleport.com>

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 Received on Mon Jun 07 1999 - 16:13:33 CDT