Re: How to select random records?

DBNerd wrote:
>
> Jerry,
>
> Thank you for replying my posting. How can you convert a rownum to a
> rowid. Maybe row number in your posting is not rownum. Can you give me
> an example? Say I have a small table has 10 rows. I need to randomly
> select 3 out of these ten rows. How do I do it?
>
> Thanks.
> Bob
> Jerry Gitomer wrote:
> >
> > Use a prime number hashing algorithm to develop a random row number,
> > convert it into a rowid, select where rowid = calculated rowid
> >
> > You will have to some experimentation to get a good handle on how
> > rowids are built. take a small table and retrieve the rowids and
> > enough other information to tell which rowid applies to which row.
> >
> > Once you have it scoped out you will can either embed the logic in
> > something that calls Oracle and retrieves each desired row in turn
> > or build a list of desired rowids and then edit the list so that
> > each desired row is called from a shell command, e,g,
> >
> > "sqlplus scott/tiger select * from table where rowid = '001.134.124';"
> >
> > Note that this is not the correct length for a rowid, but I wanted to
> > fit the silly thing into one line :-)
> >
> > Jerry
> >
> > DBNerd wrote:
> > >
> > > Hi,
> > >
> > > Is there a way to select certain number records from a table randomly?
> > > I am doing a survay application that needs to select records from table
> > > randomly. What I am doing now is to select all records then fetch them
> > > back and skip records periodically.
> > > Is there a better way to do it?
> > >
> > > Thanks.
> > > Bob

If you have a random number generator - why not just have a cursor along the lines of:

cursor x is
  select data, random_number
  from table
  order by random_number;

then just select the first "n" rows that you want...

-- 
---------------------------------------------
Connor McDonald
BHP Information Technology
Perth
Australia
email: mcdonald.connor.cs_at_bhp.com.au
"My views are not BHP ...blah blah"
---------------------------------------------
"Opera singers are the only people that when you 
 stick a knife in them, they sing, not bleed"