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 ]