List:General Discussion« Previous MessageNext Message »
From:Tom Worster Date:January 7 2010 11:40pm
Subject:Re: Probability Selects
View as plain text  
On 1/7/10 3:10 PM, "Matt Neimeyer" <matt@stripped> wrote:

> I'm trying to select names at random from a table that contains the
> name and the frequency at which it is actually used in society. The
> table is defined as follows:
> CREATE TABLE `MaleNames` (
>  `Name_ID` int(11) NOT NULL auto_increment,
>  `Name` char(50) default NULL,
>  `Frequency` decimal(5,3) default NULL,
>  PRIMARY KEY  (`Name_ID`)
> )
> Some examples:
> 1, Aaron, 0.240
> 3, Abe, 0.006
> 13, Adrian, 0.069
> What's the best way to select names at random from this but still take
> into account frequency of use?

after reading the source, i'd avoid using MySQL's RAND(). you can probably
easily get better quality pseudorandom number in your app's environment.

to get a random row, "LIMIT 1, r", where offset r is a random number between
0 and (tablelength - 1), should work.

if you must use RAND(), FLOOR(tablelength*RAND()) will work for r.

if you want a random row from a constrained subset of rows based on
frequency, e.g. "WHERE Frequency > 0.001", you can include that condition in
the query and repeat it until you get a non-empty response.

but if the constrained subset is only a small fraction of the table's rows
then it might be more efficient to compute its size (with COUNT() in another
query or a subquery) and use that in generating the offset r.

Probability SelectsMatt Neimeyer7 Jan
  • RE: Probability SelectsDaevid Vincent7 Jan
  • Re: Probability SelectsTom Worster8 Jan
  • Re: Probability SelectsBaron Schwartz11 Jan
    • Re: Probability SelectsJohan De Meersman11 Jan