Re: Easy rowid question

Russell Huntley <rhuntley_at_ix.netcom.com> wrote:
: I am trying to put a process in place that will retrieve a sample of
: rows from an answer set.
 

: I first tried:
 

: select field1
: from table1
: where mod(rowid,3) = 0;
 

: I thought this would work but returned 0 rows. After some research, I
: discovered that if row 1 doesn't meet the "mod(rowid,3) = 0" criteria,
: then row 2 will have a rowid of 1 and won't meet the criteria, etc...
 

: One solution is to alter the table by adding a rowid_col column,
: updating rowid_col with rowid, and then doing the mod(rowid_col,3) = 0.
 

: Is there a better way???
 

: Thanks, Russ.

I did this once in PL/SQL. I set up a procedure that wrote every 50th row into a temporary table with the same structure as the first table. Something like this (note: I'm doing this from memory, so it may not be 100% accurate as far as PL/SQL syntax)

(assumes A is the original table, B has the same structure)

declare

   cursor c1 is select * from a;
   r1 c1%rowtype;
   ctr1 integer;
begin

   ctr1=0;
   for r1 in c1 loop

      ctr1:=ctr1+1;
      if ctr1=50 then
         insert into b ([field list])
           r1.field... <NOTE: SYNTAX IS REALLY BAD HERE>
         ctr1=0;
      end if;

If table A is really huge, you might commit after each insert into B.

--
Patrick Connors
pmc_at_primenet.com