weighted probability

[Porl123]

I've got a database with 10 items in at the moment with IDs 1-10 and I was wondering if there's a way to distribute the chance of getting a certain item

 

"SELECT type FROM items ORDER BY RAND() LIMIT 1"

 

Is what I'm using at the moment but there's no distribution there. I need it to pick out one random item although I need it weighted to the lower end, so it's more probable to pick out items with lower IDs than higher. I'm making this in php/mysql if that helps. Can anyone think of any ways I can do this?

 

I was thinking of adding a 'chance' field in the table with the values '1,10' and the next '11,30' and so on in descending ID order like this

 

ID | TYPE  | CHANCE

10 | type10| 1,10

9  | type9  | 11,30

8  | type8  | 31,60

 

Then I could get the highest value and the lowest value, explode them and get the appropriate numbers, then get a random number with mt_rand() and do a while loop throughout all the items, exploding the chance field and seeing where the random number lies.

 

This does work but when new items are added, all the other chances have to be changed and it's just all around a lot of work. So I was wondering whether anybody could think of either a php or mysql way of getting around this.

 

Thanks in advance for any help!

[artacus]

SELECT round(pow(1 + rand() * (sqrt(10) - 1), 2)) rnum

 

[artacus]

If you add more than 10 options, swap out the sqrt(10) with sqrt(n).