Re: Select random rows?

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Mon, 08 Nov 1999 18:51:40 +0800
Message-ID: <3826AB3C.1C7F@yahoo.com>

fumi wrote:
>
> Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message news:3822BDD1.2972_at_yahoo.com...
> >
> > Typo on my part...
> >
> > The following WILL work (and you don't have to be on 8i to do it)...
> >
> > 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.
>
> If I guess right, your function RANDOM.RNDINT(n) returns a random integer less then n.
> So, You always take sample from the forward 499 rows, not from the whole table.
>
> And more, the random values may duplicate, so you can not get the exactly number of rows
> every time.

Sorry - I should have added

my random function is a variant on the linear congruence algorithm so that no rows will be repeated...

The 500 can be replaced with a number more than the ceiling for the table - I just chose that as an example

HTH
--

---

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 Mon Nov 08 1999 - 04:51:40 CST