pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: random rows
8+ messages / 6 participants
[nested] [flat]
* random rows
@ 2001-04-26 18:38 Jie Liang <jliang@ipinc.com>
2001-04-26 18:12 ` Re: random rows Ing. Roberto Andrade Fonseca <randrade@abl.com.mx>
2001-04-26 19:18 ` Re: random rows Joel Burton <jburton@scw.org>
2001-04-26 19:18 ` Re: random rows Joao Pedro M. F. Monoo <jpedro@infortrade.com.br>
2001-04-26 19:34 ` Re: random rows Josh Berkus <josh@agliodbs.com>
2001-04-26 19:53 ` Re: random rows J.H.M. Dassen (Ray) <jdassen@cistron.nl>
0 siblings, 5 replies; 8+ messages in thread
From: Jie Liang @ 2001-04-26 18:38 UTC (permalink / raw)
To: pgsql-admin@postgresql.org; +Cc: pgsql-sql
How I can return random N rows from my select stmt?
like:
e.g. what my selectee is a set of 1000 rows, I want randomly
pickup 100 of 1000.
thanks.
Jie LIANG
St. Bernard Software
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.stbernard.com
www.ipinc.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: random rows
2001-04-26 18:38 random rows Jie Liang <jliang@ipinc.com>
@ 2001-04-26 18:12 ` Ing. Roberto Andrade Fonseca <randrade@abl.com.mx>
2001-04-26 19:29 ` Re: random rows Jie Liang <jliang@ipinc.com>
4 siblings, 1 reply; 8+ messages in thread
From: Ing. Roberto Andrade Fonseca @ 2001-04-26 18:12 UTC (permalink / raw)
To: Jie Liang <jliang@ipinc.com>; +Cc: pgsql-admin@postgresql.org
On Thu, 26 Apr 2001, Jie Liang wrote:
>
> How I can return random N rows from my select stmt?
> like:
> e.g. what my selectee is a set of 1000 rows, I want randomly
> pickup 100 of 1000.
I've donne something like:
prereg_iw=# select random(), nombre from asistente order by 1 limit 10;
random | nombre
----------------------+---------------
0.000214523170243261 | ALFONSO
0.000478655100091666 | OSCAR
0.000892118085591177 | JOSE LUIS
0.000972398091560415 | IGNACIO
0.00109919300354048 | NORBERTO
0.00180558208460248 | SALVADOR
0.00196880800741204 | ANDRES NOE
0.00197171233686233 | SARA MICHELLE
0.00226354412839913 | DAVID
0.00233715307076329 | RODOLFO
(10 rows)
and it works!
Saludos,
Roberto Andrade Fonseca
randrade@abl.com.mx
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: random rows
2001-04-26 18:38 random rows Jie Liang <jliang@ipinc.com>
2001-04-26 18:12 ` Re: random rows Ing. Roberto Andrade Fonseca <randrade@abl.com.mx>
@ 2001-04-26 19:29 ` Jie Liang <jliang@ipinc.com>
2001-04-26 20:35 ` Re: random rows Joel Burton <jburton@scw.org>
0 siblings, 1 reply; 8+ messages in thread
From: Jie Liang @ 2001-04-26 19:29 UTC (permalink / raw)
To: Ing. Roberto Andrade Fonseca <randrade@abl.com.mx>; +Cc: pgsql-admin@postgresql.org
thanks.
Jie LIANG
St. Bernard Software
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.stbernard.com
www.ipinc.com
On Thu, 26 Apr 2001, Ing. Roberto Andrade Fonseca wrote:
> On Thu, 26 Apr 2001, Jie Liang wrote:
>
> >
> > How I can return random N rows from my select stmt?
> > like:
> > e.g. what my selectee is a set of 1000 rows, I want randomly
> > pickup 100 of 1000.
>
>
> I've donne something like:
>
> prereg_iw=# select random(), nombre from asistente order by 1 limit 10;
> random | nombre
> ----------------------+---------------
> 0.000214523170243261 | ALFONSO
> 0.000478655100091666 | OSCAR
> 0.000892118085591177 | JOSE LUIS
> 0.000972398091560415 | IGNACIO
> 0.00109919300354048 | NORBERTO
> 0.00180558208460248 | SALVADOR
> 0.00196880800741204 | ANDRES NOE
> 0.00197171233686233 | SARA MICHELLE
> 0.00226354412839913 | DAVID
> 0.00233715307076329 | RODOLFO
> (10 rows)
>
> and it works!
>
> Saludos,
>
> Roberto Andrade Fonseca
> randrade@abl.com.mx
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: random rows
2001-04-26 18:38 random rows Jie Liang <jliang@ipinc.com>
2001-04-26 18:12 ` Re: random rows Ing. Roberto Andrade Fonseca <randrade@abl.com.mx>
2001-04-26 19:29 ` Re: random rows Jie Liang <jliang@ipinc.com>
@ 2001-04-26 20:35 ` Joel Burton <jburton@scw.org>
0 siblings, 0 replies; 8+ messages in thread
From: Joel Burton @ 2001-04-26 20:35 UTC (permalink / raw)
To: Jie Liang <jliang@ipinc.com>; +Cc: Ing. Roberto Andrade Fonseca <randrade@abl.com.mx>; pgsql-admin@postgresql.org
On Thu, 26 Apr 2001, Jie Liang wrote:
> > I've donne something like:
> >
> > prereg_iw=# select random(), nombre from asistente order by 1 limit 10;
> > random | nombre
> > ----------------------+---------------
> > 0.000214523170243261 | ALFONSO
> > 0.000478655100091666 | OSCAR
> > 0.000892118085591177 | JOSE LUIS
> > 0.000972398091560415 | IGNACIO
> > 0.00109919300354048 | NORBERTO
> > 0.00180558208460248 | SALVADOR
> > 0.00196880800741204 | ANDRES NOE
> > 0.00197171233686233 | SARA MICHELLE
> > 0.00226354412839913 | DAVID
> > 0.00233715307076329 | RODOLFO
> > (10 rows)
How totally obvious in retrospect, and how much better of a solution than
the ones I offered. D'oh!
Thanks for posting it to the list.
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: random rows
2001-04-26 18:38 random rows Jie Liang <jliang@ipinc.com>
@ 2001-04-26 19:18 ` Joel Burton <jburton@scw.org>
4 siblings, 0 replies; 8+ messages in thread
From: Joel Burton @ 2001-04-26 19:18 UTC (permalink / raw)
To: Jie Liang <jliang@ipinc.com>; +Cc: pgsql-sql
On Thu, 26 Apr 2001, Jie Liang wrote:
>
> How I can return random N rows from my select stmt?
> like:
> e.g. what my selectee is a set of 1000 rows, I want randomly
> pickup 100 of 1000.
Interesting problem.
You might get much better responses than this, but, two ideas
that might be workable:
* use a WHERE clause that checks random() > .88 . This should
give you, on average, about 120 rows out of 1000, and you
can add LIMIT 100 to ensure that you get only 100. But you're
still biased toward the start of the list. (Or, remove the
LIMIT 100, use > .9, but there's no guarantee you'll get 100--
you'll get more or less than that.
* have a plpgsql routine that gets 100 random records,
and copy these into a temporary table (since plpgsql can't
return a recordset.) Query against this table.
Or, when all else fails:
* do it in your front end (Python/Perl/PHP/Pwhatever).
If you get better ideas, and they aren't cc'd to the list, please do so.
HTH,
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: random rows
2001-04-26 18:38 random rows Jie Liang <jliang@ipinc.com>
@ 2001-04-26 19:18 ` Joao Pedro M. F. Monoo <jpedro@infortrade.com.br>
4 siblings, 0 replies; 8+ messages in thread
From: Joao Pedro M. F. Monoo @ 2001-04-26 19:18 UTC (permalink / raw)
To: pgsql-sql
Hi!
> How I can return random N rows from my select stmt?
> like:
> e.g. what my selectee is a set of 1000 rows, I want randomly
> pickup 100 of 1000.
use the LIMIT clause
example
SELECT * FROM test_table LIMIT 100;
you can also use the OFFSET clause to skip to n row and the fetch the n
desired rows
example
SELECT * FROM test_table LIMIT 100 OFFSET 100;
this will skip to row number 100 and the fetch the next 100 rows
[]´s
---------------------------------------------
Joao Pedro M. F. Monoo
Infortrade Information Systems
#183816 Linux Registered User
Slackware 7.1 running 2.4.2 Linux Kernel
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: random rows
2001-04-26 18:38 random rows Jie Liang <jliang@ipinc.com>
@ 2001-04-26 19:34 ` Josh Berkus <josh@agliodbs.com>
4 siblings, 0 replies; 8+ messages in thread
From: Josh Berkus @ 2001-04-26 19:34 UTC (permalink / raw)
To: Jie Liang <jliang@ipinc.com>; pgsql-sql
Jie,
> How I can return random N rows from my select stmt?
> like:
> e.g. what my selectee is a set of 1000 rows, I want randomly
> pickup 100 of 1000.
You'd have to do it inside a function or external program, and copy the
rows to a temporary table (which is what you'd return to the user).
Thus, language-agnostic rules:
CREATE FUNCTION return_random(X)
LOOP X Times
totalrecs = COUNT(*) FROM maintable WHERE NOT EXISTS temptable
offset_count = RANDOM*totalrecs
INSERT INTO temptable ( a, b, c, d )
SELECT a, b, c, d FROM maintable
LIMIT 1 OFFSET offset_count;
END LOOP
END;
than:
SELECT temptable
-Josh
--
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: random rows
2001-04-26 18:38 random rows Jie Liang <jliang@ipinc.com>
@ 2001-04-26 19:53 ` J.H.M. Dassen (Ray) <jdassen@cistron.nl>
4 siblings, 0 replies; 8+ messages in thread
From: jdassen@cistron.nl @ 2001-04-26 19:53 UTC (permalink / raw)
To: pgsql-admin@postgresql.org; pgsql-sql
Jie Liang <jliang@ipinc.com> wrote:
>e.g. what my selectee is a set of 1000 rows, I want randomly pickup 100 of
>1000.
Have you tried
SELECT * FROM selectee ORDER BY random() LIMIT 100;
?o
HTH,
Ray
--
<GRub[B]eR> JHM, jij bent echt nerd :))
<GRub[B]eR> maar wel een goeie :)
<GRub[B]eR> Soort van programmerende furby
Gezien op #cistron
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2001-04-26 20:35 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-26 18:38 random rows Jie Liang <jliang@ipinc.com>
2001-04-26 18:12 ` Ing. Roberto Andrade Fonseca <randrade@abl.com.mx>
2001-04-26 19:29 ` Jie Liang <jliang@ipinc.com>
2001-04-26 20:35 ` Joel Burton <jburton@scw.org>
2001-04-26 19:18 ` Joel Burton <jburton@scw.org>
2001-04-26 19:18 ` Joao Pedro M. F. Monoo <jpedro@infortrade.com.br>
2001-04-26 19:34 ` Josh Berkus <josh@agliodbs.com>
2001-04-26 19:53 ` J.H.M. Dassen (Ray) <jdassen@cistron.nl>
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