The PerlDoc version works with a DB by treating it as a file, but
you still have the overhead of selecting and returning all that
data over a relatively slow network. An even better approach is
to randomly calculate the id to select. If your IDs are integers
and contiguous, this is relatively simple. Generate a random
number in the range of the min(id) to max(id). Generally, you
can't assume an id is numeric or its range is without gaps, so
a bit more cleverness is required:
1. Count the number of rows (e.g.: select count(*) from quotes)
2. Generate a random number between 1 and the row count.
3. Fetch the row associated with that row number.
In DB2 land, we can do this last step in SQL with the function:
ROWNUMBER() OVER(order criteria), as follows:
select
*
from
quotes,
table (
select
rownumber() over (order by id) rownum,
id
from
quotes
) as ids
where
ids.id = quotes.id
and ids.rownum = ?
The passed in parameter is the random row number that you generated
from a row count. This approach eliminates needing to read all
rows up to the desired row by gathering up the list of ids and their
row numbers in a temporary table.
I don't know the capabilities of postgresql but if you have access
to a row number of some sort and can create a temporary table, you
should be able to work up something similar. One row transmitted
is much faster and more predictable than N rows upto the random
row transmitted.
Stph
> -----Original Message-----
> From: Wolfgang Weisselberg [mailto:[EMAIL PROTECTED]]
> Sent: Monday, June 03, 2002 4:38 AM
> To: [EMAIL PROTECTED]
> Subject: Re: Select a random row?
>
>
> At 2002-06-02 00:41:01 (-0700), Jeff Zucker wrote:
>
> > my $ids = $dbh->selectcol_arrayref("
> > SELECT quoteID
> > FROM quotes
> > ");
> > my $random_id = $$ids[ rand(@$ids) ];
> [...]
>
> This one can be memory hungry -- especially for large sets.
>
> There is a well-known algorithm on how to get a random row out of
> n rows with O(n) and low memory usage.
>
> > Ilya, does this belong in a FAQ?
>
> perldoc -q "How do I select a random line from a file"
>
> -Wolfgang
>
