On 1/7/10 3:10 PM, "Matt Neimeyer" <m...@neimeyer.org> 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
