pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedrandom
19+ messages / 10 participants
[nested] [flat]
* random
@ 2001-03-05 19:12 Jelle Ouwerkerk <jelle@openface.ca>
2001-03-05 20:50 ` Re: random Tom Lane <tgl@sss.pgh.pa.us>
2001-03-07 11:50 ` Re: random Tomek Zielonka <tomek-lists@mult.i.pl>
0 siblings, 2 replies; 19+ messages in thread
From: Jelle Ouwerkerk @ 2001-03-05 19:12 UTC (permalink / raw)
To: pgsql-sql
Hi
What would be the best way to select a random row from a result set?
Possibilities:
1) o get the total number of rows using count()
o generate a random number between 1 and the total
o select the n'th row using OFFSET
2) o get the total number of rows using count()
o generate a random number between 1 and the total
o fetch n times
Option 1 uses 'OFFSET' which is a database-specific keyword, which is
probably bad.
Also, is there a way to randomize the order of a result set?
Thanks in advance
Jelle Ouwerkerk
Software Developer
Openface Internet Inc.
Montreal, Quebec, Canada
http://www.openface.ca
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: random
2001-03-05 19:12 random Jelle Ouwerkerk <jelle@openface.ca>
@ 2001-03-05 20:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-05 21:04 ` Re: random Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 19+ messages in thread
From: Tom Lane @ 2001-03-05 20:50 UTC (permalink / raw)
To: Jelle Ouwerkerk <jelle@openface.ca>; +Cc: pgsql-sql
Jelle Ouwerkerk <jelle@openface.ca> writes:
> Also, is there a way to randomize the order of a result set?
There's always
SELECT * FROM foo ORDER BY random();
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: random
2001-03-05 19:12 random Jelle Ouwerkerk <jelle@openface.ca>
2001-03-05 20:50 ` Re: random Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-03-05 21:04 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-03-05 21:20 ` Re: random Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-05 21:23 ` Re: random Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 19+ messages in thread
From: Bruce Momjian @ 2001-03-05 21:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jelle Ouwerkerk <jelle@openface.ca>; pgsql-sql
> Jelle Ouwerkerk <jelle@openface.ca> writes:
> > Also, is there a way to randomize the order of a result set?
>
> There's always
> SELECT * FROM foo ORDER BY random();
>
How does that work?
test=> select random();
random
-------------------
0.896045367650709
(1 row)
However:
test=> select * from pg_class order by random();
does return some output. Is it random, and if so, how?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: random
2001-03-05 19:12 random Jelle Ouwerkerk <jelle@openface.ca>
2001-03-05 20:50 ` Re: random Tom Lane <tgl@sss.pgh.pa.us>
2001-03-05 21:04 ` Re: random Bruce Momjian <pgman@candle.pha.pa.us>
@ 2001-03-05 21:20 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 0 replies; 19+ messages in thread
From: Stephan Szabo @ 2001-03-05 21:20 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Jelle Ouwerkerk <jelle@openface.ca>; pgsql-sql
On Mon, 5 Mar 2001, Bruce Momjian wrote:
> > Jelle Ouwerkerk <jelle@openface.ca> writes:
> > > Also, is there a way to randomize the order of a result set?
> >
> > There's always
> > SELECT * FROM foo ORDER BY random();
> >
>
> How does that work?
>
> test=> select random();
> random
> -------------------
> 0.896045367650709
> (1 row)
>
> However:
>
> test=> select * from pg_class order by random();
>
> does return some output. Is it random, and if so, how?
As a guess...
I'd assume that if random() is not marked as cachable, it
would call random() once for each output row after any
where clauses are done so it'd get different random
numbers for each row that it'd use for the sorting.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: random
2001-03-05 19:12 random Jelle Ouwerkerk <jelle@openface.ca>
2001-03-05 20:50 ` Re: random Tom Lane <tgl@sss.pgh.pa.us>
2001-03-05 21:04 ` Re: random Bruce Momjian <pgman@candle.pha.pa.us>
@ 2001-03-05 21:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-05 21:29 ` Re: random Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 19+ messages in thread
From: Tom Lane @ 2001-03-05 21:23 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Jelle Ouwerkerk <jelle@openface.ca>; pgsql-sql
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Jelle Ouwerkerk <jelle@openface.ca> writes:
> Also, is there a way to randomize the order of a result set?
>>
>> There's always
>> SELECT * FROM foo ORDER BY random();
> However:
> test=> select * from pg_class order by random();
> does return some output. Is it random, and if so, how?
Sure. The function is marked noncachable, so it gets evaluated
afresh at each row --- but only once per row, as the sort step doesn't
recompute any values. Should work just peachy, and it seems to do
so in a couple quick tests.
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: random
2001-03-05 19:12 random Jelle Ouwerkerk <jelle@openface.ca>
2001-03-05 20:50 ` Re: random Tom Lane <tgl@sss.pgh.pa.us>
2001-03-05 21:04 ` Re: random Bruce Momjian <pgman@candle.pha.pa.us>
2001-03-05 21:23 ` Re: random Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-03-05 21:29 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-03-05 21:34 ` Re: random Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 19+ messages in thread
From: Bruce Momjian @ 2001-03-05 21:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jelle Ouwerkerk <jelle@openface.ca>; pgsql-sql
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> Jelle Ouwerkerk <jelle@openface.ca> writes:
> > Also, is there a way to randomize the order of a result set?
> >>
> >> There's always
> >> SELECT * FROM foo ORDER BY random();
>
> > However:
> > test=> select * from pg_class order by random();
> > does return some output. Is it random, and if so, how?
>
> Sure. The function is marked noncachable, so it gets evaluated
> afresh at each row --- but only once per row, as the sort step doesn't
> recompute any values. Should work just peachy, and it seems to do
> so in a couple quick tests.
But random returns a random value from 0-1, right? How does that work
in ORDER BY?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: random
2001-03-05 19:12 random Jelle Ouwerkerk <jelle@openface.ca>
2001-03-05 20:50 ` Re: random Tom Lane <tgl@sss.pgh.pa.us>
2001-03-05 21:04 ` Re: random Bruce Momjian <pgman@candle.pha.pa.us>
2001-03-05 21:23 ` Re: random Tom Lane <tgl@sss.pgh.pa.us>
2001-03-05 21:29 ` Re: random Bruce Momjian <pgman@candle.pha.pa.us>
@ 2001-03-05 21:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-05 21:45 ` Re: random Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 19+ messages in thread
From: Tom Lane @ 2001-03-05 21:34 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Jelle Ouwerkerk <jelle@openface.ca>; pgsql-sql
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> But random returns a random value from 0-1, right? How does that work
> in ORDER BY?
What's the problem? Each row gets a different random value, then we
sort.
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: random
2001-03-05 19:12 random Jelle Ouwerkerk <jelle@openface.ca>
2001-03-05 20:50 ` Re: random Tom Lane <tgl@sss.pgh.pa.us>
2001-03-05 21:04 ` Re: random Bruce Momjian <pgman@candle.pha.pa.us>
2001-03-05 21:23 ` Re: random Tom Lane <tgl@sss.pgh.pa.us>
2001-03-05 21:29 ` Re: random Bruce Momjian <pgman@candle.pha.pa.us>
2001-03-05 21:34 ` Re: random Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-03-05 21:45 ` Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 19+ messages in thread
From: Bruce Momjian @ 2001-03-05 21:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jelle Ouwerkerk <jelle@openface.ca>; pgsql-sql
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > But random returns a random value from 0-1, right? How does that work
> > in ORDER BY?
>
> What's the problem? Each row gets a different random value, then we
> sort.
Oh, I see. Nifty. I am used to seeing a column name or number in ORDER
BY. Why doesn't the parser see this as a column number?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: random
2001-03-05 19:12 random Jelle Ouwerkerk <jelle@openface.ca>
@ 2001-03-07 11:50 ` Tomek Zielonka <tomek-lists@mult.i.pl>
2001-03-07 12:56 ` Re: random Tomek Zielonka <tomek-lists@mult.i.pl>
1 sibling, 1 reply; 19+ messages in thread
From: Tomek Zielonka @ 2001-03-07 11:50 UTC (permalink / raw)
To: pgsql-sql
On Mon, Mar 05, 2001 at 02:12:15PM -0500, Jelle Ouwerkerk wrote:
> Hi
>
> What would be the best way to select a random row from a result set?
>
> Possibilities:
>
> 1) o get the total number of rows using count()
> o generate a random number between 1 and the total
> o select the n'th row using OFFSET
>
> 2) o get the total number of rows using count()
> o generate a random number between 1 and the total
> o fetch n times
Here's my approach. It allows to get random row from the table, not from result
set in general. Maybe you will be able to modify it to fit your needs.
The table, which we want to get random row from, is not shown here. Let's
assume that it has primary key on integer type column. In the table rnd we keep
references to this table in value column.
Other columns are needed by our selection mechanism. We may want 'values' to be
duplicated (different weights), so there is this 'id' column which uniquely
identifies rnd's row.
CREATE SEQUENCE rnd_seq;
CREATE TABLE rnd (
id INT4 NOT NULL DEFAULT NEXTVAL('rnd_seq'),
r DOUBLE PRECISION NOT NULL DEFAULT random(),
value INT4,
PRIMARY KEY (id)
);
/* My idea is to index this table with random values.
* Then it is sufficent to take first row in this order to get a random one
* and of course we have to modify its r (random) fields not to get it again,
* and again, ...
*
* I hope you understand my explanation in weak english */
CREATE INDEX rnd_r_idx ON rnd (r);
/* This function does it. SELECT it issues is very fast, 'cause it uses an
* index */
CREATE FUNCTION get_rnd() RETURNS INT4 AS '
DECLARE
rowid INT4;
val INT4;
BEGIN
SELECT id, value INTO rowid, val
FROM rnd
ORDER BY r
LIMIT 1;
IF NOT FOUND THEN RETURN NULL; END IF;
UPDATE rnd SET r = random() WHERE id = rowid;
RETURN val;
END;
' LANGUAGE 'plpgsql';
/* This function only fills the table with test data */
CREATE FUNCTION fill() RETURNS INT4 AS '
DECLARE
i INT4;
BEGIN
i := 5555;
WHILE i <> 0 LOOP
INSERT INTO rnd (value) VALUES (i);
i := i - 1;
END LOOP;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
VACUUM ANALYZE rnd;
What do you think? Is it a good idea?
greetings,
Tom
--
.signature: Too many levels of symbolic links
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: random
2001-03-05 19:12 random Jelle Ouwerkerk <jelle@openface.ca>
2001-03-07 11:50 ` Re: random Tomek Zielonka <tomek-lists@mult.i.pl>
@ 2001-03-07 12:56 ` Tomek Zielonka <tomek-lists@mult.i.pl>
0 siblings, 0 replies; 19+ messages in thread
From: Tomek Zielonka @ 2001-03-07 12:56 UTC (permalink / raw)
To: pgsql-sql
On Wed, Mar 07, 2001 at 12:50:36PM +0100, Tomek Zielonka wrote:
> On Mon, Mar 05, 2001 at 02:12:15PM -0500, Jelle Ouwerkerk wrote:
> > Hi
> >
> > What would be the best way to select a random row from a result set?
> >
>
> Here's my approach. It allows to get random row from the table, not from result
> set in general. Maybe you will be able to modify it to fit your needs.
[cut]
> What do you think? Is it a good idea?
I tested it myself and unfortunately it doesn't work as should. I have to
rethink it.
greetings,
Tom
--
.signature: Too many levels of symbolic links
^ permalink raw reply [nested|flat] 19+ messages in thread
* Random()
@ 2006-11-16 18:31 Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2006-11-16 19:16 ` Re: Random() A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 19+ messages in thread
From: Ezequias Rodrigues da Rocha @ 2006-11-16 18:31 UTC (permalink / raw)
To: pgsql-sql
Hi list,
I have a bigint collumn and I would like to generate a random number within
the numbers of my column.
Is it possible ?
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Random()
2006-11-16 18:31 Random() Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
@ 2006-11-16 19:16 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-11-16 20:31 ` Re: Random() Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2006-11-16 20:54 ` Re: Random() Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2006-11-17 23:14 ` Re: Random() Aaron Bono <postgresql@aranya.com>
2006-11-18 17:32 ` Re: Random() Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
0 siblings, 4 replies; 19+ messages in thread
From: A. Kretschmer @ 2006-11-16 19:16 UTC (permalink / raw)
To: pgsql-sql
am Thu, dem 16.11.2006, um 16:31:14 -0200 mailte Ezequias Rodrigues da Rocha folgendes:
> Hi list,
>
> I have a bigint collumn and I would like to generate a random number within the
> numbers of my column.
select <bigint_column> from <your_table> order by random() limit 1;
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Random()
2006-11-16 18:31 Random() Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2006-11-16 19:16 ` Re: Random() A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2006-11-16 20:31 ` Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
3 siblings, 0 replies; 19+ messages in thread
From: Ezequias Rodrigues da Rocha @ 2006-11-16 20:31 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
Thank you so much andreas.
Regards
http://ezequiasrocha.blogspot.com
2006/11/16, A. Kretschmer <andreas.kretschmer@schollglas.com>:
>
> am Thu, dem 16.11.2006, um 16:31:14 -0200 mailte Ezequias Rodrigues da
> Rocha folgendes:
> > Hi list,
> >
> > I have a bigint collumn and I would like to generate a random number
> within the
> > numbers of my column.
>
> select <bigint_column> from <your_table> order by random() limit 1;
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Random()
2006-11-16 18:31 Random() Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2006-11-16 19:16 ` Re: Random() A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2006-11-16 20:54 ` Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2006-11-16 21:20 ` Re: Random() Andrew Sullivan <ajs@crankycanuck.ca>
3 siblings, 1 reply; 19+ messages in thread
From: Ezequias Rodrigues da Rocha @ 2006-11-16 20:54 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
Just another question. I can trust on this random number ? The rule to it is
quite tested ?
I am thinking in implementing a function that return allways a random
number. What do you think of the trust of this function (random()) ?
My bigint field has 20,000 numbers and I'd like to make real random (used
like lottery).
Regards
Ezequias
2006/11/16, A. Kretschmer <andreas.kretschmer@schollglas.com>:
>
> am Thu, dem 16.11.2006, um 16:31:14 -0200 mailte Ezequias Rodrigues da
> Rocha folgendes:
> > Hi list,
> >
> > I have a bigint collumn and I would like to generate a random number
> within the
> > numbers of my column.
>
> select <bigint_column> from <your_table> order by random() limit 1;
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Random()
2006-11-16 18:31 Random() Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2006-11-16 19:16 ` Re: Random() A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-11-16 20:54 ` Re: Random() Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
@ 2006-11-16 21:20 ` Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 19+ messages in thread
From: Andrew Sullivan @ 2006-11-16 21:20 UTC (permalink / raw)
To: pgsql-sql
On Thu, Nov 16, 2006 at 06:54:42PM -0200, Ezequias Rodrigues da Rocha wrote:
> My bigint field has 20,000 numbers and I'd like to make real random (used
> like lottery).
If you want _real_ random, you have to use a non-computer source for
your randomness. The best you get on a computer is pseudo-random.
Often it's very good pseudo-random -- most of the time, good enough
for most purposes -- but it isn't true randomness of the sort that is
delivered by (for example) particle decay.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The plural of anecdote is not data.
--Roger Brinner
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Random()
2006-11-16 18:31 Random() Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2006-11-16 19:16 ` Re: Random() A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2006-11-17 23:14 ` Aaron Bono <postgresql@aranya.com>
3 siblings, 0 replies; 19+ messages in thread
From: Aaron Bono @ 2006-11-17 23:14 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
On 11/16/06, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
>
> am Thu, dem 16.11.2006, um 16:31:14 -0200 mailte Ezequias Rodrigues da
> Rocha folgendes:
> > Hi list,
> >
> > I have a bigint collumn and I would like to generate a random number
> within the
> > numbers of my column.
>
> select <bigint_column> from <your_table> order by random() limit 1;
Is this a PostgreSQL thing or is it SQL standard?
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Random()
2006-11-16 18:31 Random() Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2006-11-16 19:16 ` Re: Random() A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2006-11-18 17:32 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-11-18 17:52 ` Re: Random() A. Kretschmer <andreas.kretschmer@schollglas.com>
3 siblings, 1 reply; 19+ messages in thread
From: Rajesh Kumar Mallah @ 2006-11-18 17:32 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
On 11/17/06, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
> am Thu, dem 16.11.2006, um 16:31:14 -0200 mailte Ezequias Rodrigues da
> Rocha folgendes:
> > Hi list,
> >
> > I have a bigint collumn and I would like to generate a random number
> within the
> > numbers of my column.
>
> select <bigint_column> from <your_table> order by random() limit 1;
This query will tend to get slower as the table grows because of the sorting.
it possible to get a row from a random offset
how about
select <bigint_column> from <your_table> limit 1 offset 20000*random() ;
?
regds
mallah.
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Random()
2006-11-16 18:31 Random() Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2006-11-16 19:16 ` Re: Random() A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-11-18 17:32 ` Re: Random() Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
@ 2006-11-18 17:52 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-11-18 18:45 ` Re: Random() Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: A. Kretschmer @ 2006-11-18 17:52 UTC (permalink / raw)
To: pgsql-sql
am Sat, dem 18.11.2006, um 23:02:33 +0530 mailte Rajesh Kumar Mallah folgendes:
> >select <bigint_column> from <your_table> order by random() limit 1;
>
> This query will tend to get slower as the table grows because of the
> sorting.
Right.
>
> it possible to get a row from a random offset
> how about
>
> select <bigint_column> from <your_table> limit 1 offset 20000*random() ;
For instance, you have a table with only 500 rows and random() returns
0.999? In other words: in think, this is a bad idea, unless you use a
constant for the offset-calculation thats not larger than the number of
rows in this table. For this, you need this value, but select count(1)
from table force a seqscan -> slow.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Random()
2006-11-16 18:31 Random() Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2006-11-16 19:16 ` Re: Random() A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-11-18 17:32 ` Re: Random() Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-11-18 17:52 ` Re: Random() A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2006-11-18 18:45 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
0 siblings, 0 replies; 19+ messages in thread
From: Rajesh Kumar Mallah @ 2006-11-18 18:45 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
On 11/18/06, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
>
> am Sat, dem 18.11.2006, um 23:02:33 +0530 mailte Rajesh Kumar Mallah
> folgendes:
> > >select <bigint_column> from <your_table> order by random() limit 1;
> >
> > This query will tend to get slower as the table grows because of the
> > sorting.
>
> Right.
>
>
> >
> > it possible to get a row from a random offset
> > how about
> >
> > select <bigint_column> from <your_table> limit 1 offset 20000*random()
> ;
>
> For instance, you have a table with only 500 rows and random() returns
> 0.999? In other words: in think, this is a bad idea, unless you use a
> constant for the offset-calculation thats not larger than the number of
> rows in this table. For this, you need this value, but select count(1)
> from table force a seqscan -> slow.
but order by random() would also forces a seqscan , looks like the random
offset method performs
better
explain analyze select count(*) from general.profile_master;
+--------------------------------------------------------------------------------------------------------------------------------+
| QUERY
PLAN |
+--------------------------------------------------------------------------------------------------------------------------------+
| Aggregate (cost=139214.26..139214.27 rows=1 width=0) (actual time=
3071.178..3071.179 rows=1 loops=1) |
| -> Seq Scan on profile_master (cost=0.00..137703.21 rows=604421
width=0) (actual time=0.032..2686.842 rows=601240 loops=1) |
| Total runtime: 3071.268ms
|
+--------------------------------------------------------------------------------------------------------------------------------+
(3 rows)
tradein_clients=> explain analyze SELECT profile_id from
general.profile_master limit 1 offset 601240*random();
+--------------------------------------------------------------------------------------------------------------------------------+
| QUERY
PLAN |
+--------------------------------------------------------------------------------------------------------------------------------+
| Limit (cost=13770.30..13770.53 rows=1 width=4) (actual time=
1614.146..1614.147 rows=1 loops=1) |
| -> Seq Scan on profile_master (cost=0.00..137703.21 rows=604421
width=4) (actual time=0.036..1375.742 rows=429779 loops=1) |
| Total runtime: 1614.187ms
|
+--------------------------------------------------------------------------------------------------------------------------------+
(3 rows)
tradein_clients=> explain analyze SELECT profile_id from
general.profile_master order by random() limit 1;
+--------------------------------------------------------------------------------------------------------------------------------------+
| QUERY
PLAN |
+--------------------------------------------------------------------------------------------------------------------------------------+
| Limit (cost=207079.39..207079.40 rows=1 width=4) (actual time=
11715.694..11715.695 rows=1 loops=1) |
| -> Sort (cost=207079.39..208590.45 rows=604421 width=4) (actual time=
11715.691..11715.691 rows=1 loops=1) |
| Sort Key:
random()
|
| -> Seq Scan on profile_master (cost=0.00..139214.26 rows=604421
width=4) (actual time=0.036..4605.259 rows=601241 loops=1) |
| Total runtime: 11716.039ms
|
+--------------------------------------------------------------------------------------------------------------------------------------+
(5 rows)
Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2006-11-18 18:45 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-05 19:12 random Jelle Ouwerkerk <jelle@openface.ca>
2001-03-05 20:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-05 21:04 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-03-05 21:20 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-05 21:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-05 21:29 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-03-05 21:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-05 21:45 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-03-07 11:50 ` Tomek Zielonka <tomek-lists@mult.i.pl>
2001-03-07 12:56 ` Tomek Zielonka <tomek-lists@mult.i.pl>
2006-11-16 18:31 Random() Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2006-11-16 19:16 ` Re: Random() A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-11-16 20:31 ` Re: Random() Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2006-11-16 20:54 ` Re: Random() Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2006-11-16 21:20 ` Re: Random() Andrew Sullivan <ajs@crankycanuck.ca>
2006-11-17 23:14 ` Re: Random() Aaron Bono <postgresql@aranya.com>
2006-11-18 17:32 ` Re: Random() Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-11-18 17:52 ` Re: Random() A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-11-18 18:45 ` Re: Random() Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
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