Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Randomizing resultssets

Re: Randomizing resultssets

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: 2000/08/06
Message-ID: <965570594.27100.0.nnrp-08.9e984b29@news.demon.co.uk>#1/1

Just an idea, not tested,
but if you are using 8.1.5
you could look at the SAMPLE
function:

    select * from tabx SAMPLE (0.001) rows;

You would have to know roughly
how many rows in the table
to get the right sort of size, then
select only the first 6 (or repeat the
select if the sample was too small).

(Have to be 'execute immediate' native
dynamic sql too, probably, I don't think PL/SQL recognises SAMPLE yet).

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Connor McDonald wrote in message <398D01FE.4BCC_at_yahoo.com>...

>davidai_at_shopnow.com wrote:
>>
>> I want to write a stored procedure that returns a set of 6 records
>> randomly selected from a table. What is the best way of implementing
>> this?
>>
>> David
>>
>> Sent via Deja.com http://www.deja.com/
>> Before you buy.
>
>Not spectacularly efficient, but you can do
>
>select *
>from

> select *
> from table
> order by <x> )
>where rownum < 7
>
>where <x> is the random number generator function of your choice..
>
>HTH
>--
>===========================================
>Connor McDonald
>http://www.oracledba.co.uk
>
>We are born naked, wet and hungry...then things get worse
Received on Sun Aug 06 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US