Choosing a random entry in a table by "weight"

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

•  Subject
• Author
• Posted on
I have a MySQL table of servers, I use RAND() to pick a random server
to use each time, but how can I add a number to each server entry that
allows it be to picked more often than the other 20 servers?

For example Server1's weight is 80 and Server2's weight is 40 and
hence Server1 is more likely to be picked than the others.

Re: Choosing a random entry in a table by "weight"

I dont think myqsl can do that  (corect me if im wrong please)

If the weights aren't too large i would solve it something like this:

Offcourse this will get verry ineffective if a=1,b=100000000, etc

a->1
b->3
c->2

->create a new array \$w=(a,b,b,b,c,c)

and voilla

--
Arjen
http://www.hondenpage.com - mijn site over honden

Floortje wrote:

Whoops

Re: Choosing a random entry in a table by "weight"

In theory like this: pick a random weight and take into group all the
items that weigh more, it's more likely that heavier items are taken
into the group, than the lighter. From the group you have created, pick
one randomly. If no items were in the group, your random weight was too
big, so you might limit it to < maxweight, so that always at least one
item is picked.

Transforming this into a query is another thing. This is very rough
example but you'll get the idea, I suppose:

SELECT * FROM ( SELECT * FROM servers WHERE weight > RAND() ) ORDER BY
RAND() LIMIT 1,1

--
Rami.Elomaa@gmail.com

"Wikipedia on vähän niinq internetin raamattu, kukaan ei pohjimmiltaan
usko siihen ja kukaan ei tiedä mikä pitää paikkansa." -- z00ze

Re: Choosing a random entry in a table by "weight"

Rami Elomaa wrote:

Nice .. real nice but wrong :-(

say you have this
a-2
b-1
c-1
d-1

Valid random weight numbers are 1 and 2 (both 50%)

Then your chances of picking A are 62.5% while B,C,D only get a 12.5%
change of being picked (instead of 40-20-20-20)

--
Arjen
www.hondenpage.com

Re: Choosing a random entry in a table by "weight"

Floortje kirjoitti:

I don't know what would make it particularily "wrong", it does basicly
what it should, but the distribution of percentages is different. How
would I know what is "wrong" and what "right", so I just say it's
"different". The original question does not define exactly what is "more
often than the others".

--
Rami.Elomaa@gmail.com

"Wikipedia on vähän niinq internetin raamattu, kukaan ei pohjimmiltaan
usko siihen ja kukaan ei tiedä mikä pitää paikkansa." -- z00ze

Re: Choosing a random entry in a table by "weight"

Rami Elomaa schreef:

The op asked for 'weighted'. I really like your solution but it is not
weighted. http://en.wikipedia.org/wiki/Weight_function

--
Arjen
http://www.hondenpage.com

Re: Choosing a random entry in a table by "weight"

The op asked for 'weighted'. I really like your solution but it is not
weighted. http://en.wikipedia.org/wiki/Weight_function

--
Arjen
http://www.hondenpage.com

Re: Choosing a random entry in a table by "weight"

On May 27, 12:10 pm, alpha.be...@googlemail.com wrote:

You could also do something like:

select serverId from server order by rand() / weight limit 1

Re: Choosing a random entry in a table by "weight"

SELECT *, weight*RAND() as result FROM Servers ORDER BY result LIMIT 1

Re: Choosing a random entry in a table by "weight"

Alexey Kulentsov wrote:

... ORDER BY result DESC ...

Re: Choosing a random entry in a table by "weight"

Kimmo Laine wrote:

Unless next_page.php generates PHP, the script with this include will
only get HTML.

next_page.php

<?php
if (isset(\$_GET['foo'])) {
echo '<?php echo \$_GET[\'foo\']; ?>';
} else {
echo '<?php echo \'Not available\'; ?>';
}

--