pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedrandom tuple
5+ messages / 3 participants
[nested] [flat]
* random tuple
@ 1998-10-07 08:51 Mauro Bartolomeoli <mbarto@novacomp.it>
0 siblings, 0 replies; 5+ messages in thread
From: Mauro Bartolomeoli @ 1998-10-07 08:51 UTC (permalink / raw)
To: pgsql-sql
Is there any way to select a random row from a table using an SQL query?
I'm using Postgresql 6.3.2.
---
------------------------------------
Mauro Bartolomeoli
e-mail: mbarto@novacomp.it
ICQ#: 9602542
------------------------------------
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: [SQL] random tuple
@ 1998-10-07 16:38 Jackson, DeJuan <djackson@cpsgroup.com>
1998-10-08 12:04 ` RE: [SQL] random tuple Mauro Bartolomeoli <mbarto@novacomp.it>
0 siblings, 1 reply; 5+ messages in thread
From: Jackson, DeJuan @ 1998-10-07 16:38 UTC (permalink / raw)
To: Mauro Bartolomeoli <mbarto@novacomp.it>; pgsql-sql
> Is there any way to select a random row from a table using an SQL
> query?
> I'm using Postgresql 6.3.2.
>
I'd look at using cursors and random().
-DEJ
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: [SQL] random tuple
1998-10-07 16:38 RE: [SQL] random tuple Jackson, DeJuan <djackson@cpsgroup.com>
@ 1998-10-08 12:04 ` Mauro Bartolomeoli <mbarto@novacomp.it>
0 siblings, 0 replies; 5+ messages in thread
From: Mauro Bartolomeoli @ 1998-10-08 12:04 UTC (permalink / raw)
To: Jackson, DeJuan <djackson@cpsgroup.com>; +Cc: pgsql-sql
On 07-Oct-98 Jackson, DeJuan wrote:
>> Is there any way to select a random row from a table using an SQL
>> query?
>> I'm using Postgresql 6.3.2.
>>
> I'd look at using cursors and random().
It's a good idea. Do you (or someone else) know how to use the following
PostgreSQL functions: oidrand(oid,int4) ,oidsrand(int4)? What are they
intended for and what is their result?
---
------------------------------------
Mauro Bartolomeoli
e-mail: mbarto@novacomp.it
ICQ#: 9602542
------------------------------------
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: [SQL] random tuple
@ 1998-10-08 16:46 Jackson, DeJuan <djackson@cpsgroup.com>
1998-10-09 04:43 ` Re: [HACKERS] RE: [SQL] random tuple Thomas G. Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 1 reply; 5+ messages in thread
From: Jackson, DeJuan @ 1998-10-08 16:46 UTC (permalink / raw)
To: Mauro Bartolomeoli <mbarto@novacomp.it>; +Cc: pgsql-sql@postgreSQL.org, PostgreSQL Hackers Mailing List <pgsql-hackers@hub.org>
> On 07-Oct-98 Jackson, DeJuan wrote:
> >> Is there any way to select a random row from a table using an SQL
> >> query?
> >> I'm using Postgresql 6.3.2.
> >>
> > I'd look at using cursors and random().
>
> It's a good idea. Do you (or someone else) know how to use the
> following
> PostgreSQL functions: oidrand(oid,int4) ,oidsrand(int4)? What are they
> intended for and what is their result?
>
Never seen them before but it looks like:
oidsrand(int4) -- seeds the random number generator for oidrand to
use
oidrand(oid, int4) -- returns a psudo-random oid
The parameters to oidrand I can't figure out.
Anybody else?
-DEJ
> ---
>
> ------------------------------------
> Mauro Bartolomeoli
> e-mail: mbarto@novacomp.it
> ICQ#: 9602542
> ------------------------------------
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [HACKERS] RE: [SQL] random tuple
1998-10-08 16:46 RE: [SQL] random tuple Jackson, DeJuan <djackson@cpsgroup.com>
@ 1998-10-09 04:43 ` Thomas G. Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 0 replies; 5+ messages in thread
From: Thomas G. Lockhart @ 1998-10-09 04:43 UTC (permalink / raw)
To: Jackson, DeJuan <djackson@cpsgroup.com>; +Cc: Mauro Bartolomeoli <mbarto@novacomp.it>; pgsql-sql@postgreSQL.org, PostgreSQL Hackers Mailing List <pgsql-hackers@hub.org>
> > >> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~1998-10-09 04:43 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-10-07 08:51 random tuple Mauro Bartolomeoli <mbarto@novacomp.it>
1998-10-07 16:38 RE: [SQL] random tuple Jackson, DeJuan <djackson@cpsgroup.com>
1998-10-08 12:04 ` Mauro Bartolomeoli <mbarto@novacomp.it>
1998-10-08 16:46 RE: [SQL] random tuple Jackson, DeJuan <djackson@cpsgroup.com>
1998-10-09 04:43 ` Thomas G. Lockhart <lockhart@alumni.caltech.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox