| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Select random rows?
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>
--
"Some days you're the pigeon, and some days you're the statue." Received on Fri Nov 05 1999 - 05:20:49 CST
 |
 |