Thread: How do I randomize with higher rating on some parts?

1. How do I randomize with higher rating on some parts?

I have a list of data stored in a database that I want to come up on a page randomly. I have some that I want to come up more often than others. I have them rated on a scale of 1 to 10. I want the 10's to show up most often and the 1's less often. How do I get this to happen?

The rating won't be in whole numbers. may be 3.2 or 7.333333..... etc.

2.

3. I forgot to say, I'm using php and MySQL

4. You're looking for what's known as a weighted random sample. Here's how you handle it in general terms (PHP/MySQL is not a specific implementation I've tried).

1) Add up all the ratings to make one big number.
2) Pick a number at random between 0 and the big number.
3) Go through your list of data, starting with the biggest numbers first, until you get to a number that is greater than your random number (or until you get to the end of the list).
4) Show whatever record that is.

The bigger ratings will not only be more likely to show up because of their relation to the big number, but also since you're starting from the biggest number and working downward.

5. Glenn, how many total rows would you expect to have in your database at the most? 1000, 100000?

I think the method to list weighted random would be different depending on table size.

Also, I think you are trying to display the entire list randomly, but weighted? Or are you just picking one row from the list?

@TheGame,
For picking one number (or row), that might be OK, but what if the entire list, or the first 10 of them need to be displayed? I'm not sure how you would generate a complete list from MySQL.

EDIT:
I of course Googled this too ...

From all of the results, I thought this one was the best (untested).

SELECT * FROM `table` ORDER BY -LOG(1.0 – RAND())/rating

This will result in a random listing of your whole list by weighted "rating" 0.1 to 10.0
The rating must be greater than 0.0, so 0.1 would be the smallest rating. You mentioned that your rating starts at 1.0, so that won't be an issue.

The key point to all of my findings are that RAND produces a real number between 0 and 1. The word "between" would indicate that the value would never be 0 or would never be 1.

6. mlseim: the way I've handled it through MS-SQL is usually through a stored procedure. A sum query to get the weighted random sum, generate the random number, a query to retrieve the associated record, subtract the weight from the random sum, repeat steps 2-4 as needed. It has worked well with several hundred thousand records.

Hence the reason I said "in general terms". You know PHP and I. We don't play nicely together.