pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: 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