you could add a weight field(float value) to each monster and do

SELECT name FROM monsters ORDER BY RAND()*weight LIMIT 1 ORDER DESC

Edit: ORDER BY -LOG(1.0-RAND())/weight should give a better distribution (just multiplying by weight would greatly skew the results towards high weight rows and the skewing will get worse as you add more rows to the table)

So basicly you just multiply your monster result with the weight field number and monsters will be listed descending based upon the highest result number?

Those on top will return result more often I assume but is this still random? I mean will all of the results have some chance to be picked?

RAND() returns a value between 0 and 1, so when you do ORDER BY RAND() it assigns a random value between 0 and 1 to each row , sorts the table based on that random value (lowest first by default) and LIMIT 1 then restricts the query to only return the first row.

My example probably wasn't that good though as having a single row with a weight 10 and a million rows with weight 1 will result in the weight10 row being picked ~90% of the time (as long as its RAND() result is above 0.1 its total value will be higher than 1.0(which is the highest a weight 1 row can get).

you're probably better off doing:

SELECT name FROM monsters ORDER BY -LOG(1.0-RAND())/weight

(This should, AFAIK result in a good distribution where a 1.0 weight row gets selected roughly twice as often as a 0.5 weight row)

Thanks, do you think above algorithm is random enough even for more rows with bigger values?

it should be, i did a quick test with 5 rows and did 1000 random weighted selects:

row0: weight 0.33 : selected 143 times

row1: weight 0.5 : selected 225 times

row2: weight 1.0 : selected 448 times

row3: weight 0.33 : selected 141 times

row4: weight 0.1 : selected 43 times

the 0.33 ones got selected roughy equally, and roughly 1/3rd as frequently as the 1.0 row

the 0.5 row got selected roughly half as often as the 1.0 row, and the 0.1 row got selected roughly 1/10th as often as the 1.0 row.

throwing in a row5 with a weight of 2.0 and 10.000 repeats running twice (to give better stats) gives me

row0: weight 0.33: selected 811 and 751 times (roughly 33% as often as 1.0)

row1: weight 0.5: selected 1181 and 1213 times (roughly half as often as 1.0)

row2: weight 1.0: selected 2345 and 2372 times

row3: weight 0.33: selected 740 and 805 times (roughly the same as row0 which had the same weight)

row4: weight 0.1: selected 220 and 230 times (roughly 10% as often as 1.0)

row5: weight 2.0 selected 4703 and 4729 times (roughly twice as often as the 1.0)

so it seems quite stable regardless of weights used. (i havn't tested it with thousands of rows or insanely high weights but unless my math is completely off it should behave correctly for such cases as well)