Re: Random Sample Using SQL?

Kendall Willets (TV DBA) wrote:
>
 

>
> How about using a random function of ROWNUM? That would provide you
> with a unique integer for each row, so you should be able to get a
> decent pseudo-random sequence out of them.
>
> eg:
>
> SELECT * FROM EMP
> WHERE RANDOM_FN( ROWNUM ) <= .10
>
> You only need a seed if you don't want RANDOM_FN to repeat
> over subsequent queries. For example, in the
> above setup RANDOM_FN(3) would return the same value on every query,
> unless we find a way to put in random seed before we start (like using
> RANDOM_FN( ROWNUM + SEED ) where SEED is changed on each query).
>

This won't work. Consider: until Random_fn(n)<=.10, ROWNUM will never reach n+1. And unless Random_fn(1)<=.10 you generate *nothing* at all. The only way for the random number to change between calls is for it to use some value which changes between calls. You can get such a number from the time, if you can extract a sufficiently small time interval out; I don't think you can currently do this in oracle. (time granularity is to the second). However, creating a numeric hash from a string representation of a rowid, and the time, could give you a pretty random number. I'll leave this as an excercise ;o)

>
> I believe the above will give randomness equivalent to the original
> package.

In a previous post to this newsgroup I posted another random number generator with a longer period than yours, though it too has side effects. Then again, I never intended it to be used from sql.

http://xp7.dejanews.com/getdoc.xp?recnum=5902832&server=db97p3&CONTEXT=867943814.17382&hitnum=19

-- 
****====----                                              Brian Ewins. 
Fax: (44) 141 220 6100                          Tel: (44) 141 220 6121
 "It's time we face reality, my friends...
      We're not exactly rocket scientists." --Gary Larson ----====****