Selecting random rows

[Steve Chernyak]

I need to select 4 random rows from a table that contains approximatly 72,000 rows.
Is there a way to do it efficently without making the code database dependant.
If not then Im using Oracle 8.1.7
My first thought was to do it this way:
select everything
go to the last row and get the row number n
generate four random numbers from 0 to n
retrieve the records at those locations
I would like to somehow select 4 random rows instead of everything. There are no rows in the table that can be used as keys for this.
Thanks in advance.
Steve

[Jamie Robertson]

can you add a column to the table to have have one column that is a number generated by a sequence starting at 1? If so, you can just use "select count(*) from table". create the random numbers between between 1 and the returned count. Then do a "select ... from table where new_column = " + random_number1 + " OR new_column = " random_number2 + .....
Otherwise, you could do a "select count(*) from table" to find the number of rows in the table. Again, generate 4 random numbers in java between 1 and the row count. sort the random numbers in ascending order ( 1, 2, 3....). Loop through the resultset retreiving only the rows that are equal to the random numbers
something like:

int row_count = // execute jdbc to attain row count
int[] random_numbers = new int[4];
for ( int i = 0; i < 4; i++ )
{
   random_numbers[i] = //assign a random number between 1 and row_count
}
sort( random_numbers ); //your method to sort ints
ResultSet rs = stmt.executeQuery ( "SELECT * FROM table" );
int counter = 1;
while ( rs.next() )
{
   for ( int i = 0; i < 4; i++ )
   {
      if ( counter == random_numbers[i] )
      {
         //retrieve resultset info as it matches random number
      }
   }
counter++;
} 

as for any easy tricks, I don't know of any.
Jamie
[ June 17, 2002: Message edited by: Jamie Robertson ]
[ June 17, 2002: Message edited by: Jamie Robertson ]

[Michael Matola]

Originally posted by Jamie Robertson:
can you add a column to the table to have have one column that is a number generated by a sequence starting at 1? If so, you can just use "select count(*) from table". create the random numbers between between 1 and the returned count. Then do a "select ... from table where new_column = " + random_number1 + " OR new_column = " random_number2 + .....

Note that if you're working in Oracle and you don't have the luxury of adding the column Jamie describes, there is a workaround. You can fake a row number to qualify on by using the Oracle pseudocolumn rownum and an inline view.
The typical use of rownum is to limit the number of rows returned by a query by an arbitrary number of rows (however the results may or may not be sorted). Note that rownum is generated by the query and does not purport to represent how the underlying rows may be stored on the table. (Read the docs on rownum to understand the interaction between rownum and order by.) The following query returns the first 5 rows generated by the query.
select rownum , e.* from etable e where rownum < 6 ;
Note that you cannot use rownum with = (or > to pick a row based on rownum
select e.* from etable e where rownum = 3 ; /* Returns zero rows */
because Oracle fetches a row, assigns it rownum 1, that row fails the condition of rownum = 3 and is discarded. Another row it fetched and assigned rownum 1, fails the condition and is discarded, etc.
UNLESS you generate the rownum as an inline view and qualify on the aliased column in the enclosing select:
select mm
from (
select rownum mm
from etable e )
where mm = 3

Note that to actually get the fields from the inline view, you seem to need to alias them individually:
select mm , field1 , field2 , field3
from (
select rownum mm ,
e.field1 field1 ,
e.field2 field2 ,
e.field3 field3
from etable e )
where mm = 3

Now instead of qualifying on mm = 3, you'd qualify on the random numbers you generated in the calling routine like Jamie mentioned.
[ June 17, 2002: Message edited by: Michael Matola ]