Weighted Random

[Pezmc]

I have a mysql query querying my database using order by RAND() LIMIT 1 to sellect a random coice from the database, however I would like to have the chance of a object being chosen increased for certain values (Weighting?)

In my database I have:

id  name weight
1  Pezm  1
2  Shog  1.1
3  Ebay  1.2
4  PLp   1.3
5  php  1.4

So follwing the information in my database if a weighted random script is used php shuld be the most frequent with 1.4*the chance to get chosen, compared to Pezm,

How can this be acchieved?

[stolzyboy]

a quick google and i found this thread: http://forums.devarticles.com/database-development-6/weighted-random-selection-in-postgresql-8783.html

which contained the following suggestion, it is using postgresql, but i don't see why it wouldn't work with mysql, i'd give it a whirl, i can't test it for you from here so you'll have to do some legwork 🙂

SELECT [FieldsList]
FROM [TableName]
WHERE [WhereStatements]
ORDER BY Rand()*(1/Weight) LIMIT 1;

Assuming your weight field is an int, larger value has more weight, and named "Weight"

hth,
stolzyboy

[Pezmc]

Wow thanks that is really use full,
what did you google to find that? I spent about 30 mins searching before resulting to asking in a forum 🙂

[bradgrafelman]

The term "mysql weighted random" came up with a few results that use the same suggestion as stolzyboy found.

EDIT: Note that using 'ORDER BY RAND()' can have some performance hits depending upon table size.