Re: Select random rows?

From: Alan Shein <alanshein_at_erols.com>
Date: Fri, 5 Nov 1999 14:56:18 -0500
Message-ID: <7vvcnj$13e$1@autumn.news.rcn.net>

This will work:

select rownum, <column>
from <table>
where rownum < 41
having rownum > 19
group by rownum, <column>

Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message news:3822BD91.47C5_at_yahoo.com...
> Jeff Guttadauro wrote:
> >
> > Hi, Connor.
> >
> > I'm confused... Wouldn't this return no rows when xxx > the #
> > of rows in table a and only 1 row when xxx <= the # of rows in table
> > a, since the "each_row = xxx" condition will restrict the result set
> > to that row having a rownum value of xxx from the subquery?
> >
> > Maybe the query is missing something... Or maybe I'm missing
> > something...
> >
> > -Jeff
> >
> > On Thu, 04 Nov 1999 19:10:52 +0800, Connor McDonald
> > <connor_mcdonald_at_yahoo.com> wrote:
> >
> > >kev wrote:
> > >>
> > >> Hi,
> > >>
> > >> How do I select, say, 10 random rows from a dataset?
> > >>
> > >> - Kev
> > >
> > >(if your table is not too large...)
> > >
> > >select *
> > >from ( select rownum each_row, a.*
> > > from table a )
> > >where each_row = xxx
> > >and rownum < 10
> > >
> > >where 'xxx' is whatever appropriate random number generator you want
> > >(eg dbms_random)
> > >--
> > >===========================================
> > >Connor McDonald
> > >"These views mine, no-one elses etc etc"
> > >connor_mcdonald_at_yahoo.com
> > >
> > >"Some days you're the pigeon, and some days you're the statue."

>

> Sorry - typo on my part...the following works...

>

> SQL> create table test1 ( x number );

>

> Table created.

>

> SQL> insert into test1 select rownum from all_objects
> 2 where rownum < 999;

>

> 998 rows created.

>

> SQL> commit;

>

> Commit complete.

>

> SQL> select *
> 2 from ( select rownum xxx, a.* from test1 a)
> 3 where xxx in ( select random.rndint(500)
> 4 from test1
> 5 where rownum < 10 )
> 6 /

>


>       XXX         X


> --------- ---------


>        15        15


>        69        69


>       133       133


>       160       160


>       167       167


>       221       221


>       338       338


>       350       350


>       450       450


>

> 9 rows selected.

>

> SQL>
> --
> ===========================================
> Connor McDonald
> "These views mine, no-one elses etc etc"
> connor_mcdonald_at_yahoo.com

>
> "Some days you're the pigeon, and some days you're the statue."
Received on Fri Nov 05 1999 - 13:56:18 CST