Received: from golem.jpl.nasa.gov (root@hectic-2.jpl.nasa.gov [128.149.68.204]) by hub.org (8.8.8/8.8.8) with ESMTP id AAA18819 for ; Fri, 9 Oct 1998 00:42:58 -0400 (EDT) (envelope-from lockhart@alumni.caltech.edu) Received: from alumni.caltech.edu (localhost [127.0.0.1]) by golem.jpl.nasa.gov (8.8.5/8.8.5) with ESMTP id EAA13940; Fri, 9 Oct 1998 04:43:55 GMT Sender: tgl@mythos.jpl.nasa.gov Message-ID: <361D948B.9612C41C@alumni.caltech.edu> Date: Fri, 09 Oct 1998 04:43:55 +0000 From: "Thomas G. Lockhart" Organization: Caltech/JPL X-Mailer: Mozilla 4.07 [en] (X11; I; Linux 2.0.30 i686) MIME-Version: 1.0 To: "Jackson, DeJuan" CC: Mauro Bartolomeoli , pgsql-sql@postgreSQL.org, PostgreSQL Hackers Mailing List Subject: Re: [HACKERS] RE: [SQL] random tuple References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit > > >> Is there any way to select a random row from a table? > > > I'd look at using cursors and random(). > > Do you (or someone else) know how to use the following > > PostgreSQL functions: oidrand(oid,int4) ,oidsrand(int4)? > oidsrand(int4) -- seeds the random number generator for oidrand > oidrand(oid, int4) -- returns a psudo-random oid The regression test uses oidrand(), which is where I stumbled across it. The behavior is that oidrand() returns a boolean true/false with an inverse probability specified by the second argument. For example, given a table t with 100 entries, and the query select * from t where oidrand(oid, 10) will return, on average, 10 (10%) of the entries at random. The function is called 100 times in the course of the query, and uses random() or something similar to decide whether to return true or false for any particular instance. select * from t where oidrand(oid, 1) will, on average, return all entries (1/1 = 100%). select * from t where oidrand(oid, 100) will, on average, return 1 entry (1/100 = 1%) so sometimes will return one, zero, or two entries, and occasionally return more than two entries. It's pretty random, probably with a Poisson distribution depending on what you are asking for. Presumably oidsrand() allows one to change the seed to keep the pseudo-random results from repeating from one run to the next. But I haven't looked into it. - Tom