Re: Select random rows?

alan, rownum > 19 will not work. it will give you a result of no row selected. there was a discussion at this forum about why it does not work sometime back.

SQL> select count(*) from t;

 COUNT(*)

      118

SQL> select * from t where rownum > 10 and rownum < 20;

no rows selected
ted chyn
In article <7vvcnj$13e$1_at_autumn.news.rcn.net>,   "Alan Shein" <alanshein_at_erols.com> wrote:
> 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."
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Nov 09 1999 - 08:24:34 CST