Selecting items by weight

Anon

Anyone have a way for selecting items from a database and sort/select them according to a weight value? For example you'd have a 'weight' column with a value 0-100 - a row with weight of '60' would get selected 60% of the time on average.

ace21

Grab a random number between 1-100 and see if weight is greater than it. Ran enough times 60 would come up approximately 60% of the time.

Ace21

amcgrath

Your example makes no sense, or I am completely clueless. a record with a value of 60 would not be selected 60 percent of the time on average. You don't tell us how many rows you have, for example. This, no doubt, will affect the the law of averages. Can you be a little more specific?

Anon

As Allen wrote, it seems that some info is lacking. Tho, my eyes fell on this part in the first post:

"Anyone have a way for selecting items from a database and sort/select them according to a weight value?"

And yes, there is (If I'm not clueless). If you check the manuals, you'll find "order by", "count()", "max()", "min()" and so on. Even database functions to get average numbers.

So I guess an example in your case could be: "select * from table order by weight"

Again, if I understood you allright...

Anon

Let me try again - like a banner/ad rotation system. You assign weights to items, like 50, 60, 80, etc. Then over the long run the likelihood of their getting selected is based on that weight. I'm guessing you could do something like:

select from items
order by rand() weight

Just wondering if people have done something similar...

amcgrath

OK, now I understand what you mean... one way would be to have weight table and an ad table. The wieght table would have X rows inserted based on the assigned weight. When looking for an ad, you could select a random one from that table (joined with the ad table) and since the ad has X rows entered, it would have X percent chance of being picked.

I'm not sure if that's perfect, but I think it would work.

jendiaz

Hi Allen & CSN & all,

Emmmmmm....may be could u plz make ur explanation more clearer. How if my ads table will capture the weight as well?

Example:

My banner table has the structure as below:

bannerId
bannerName
bannerweight (1 - 10)

How should I select the banner out randomly according to their weight (higher weight appear the most) other than use SQL as below:

select from banner order by RAND()bannerweight

Well, I don't think that control the result from the sql statement is a good way becoz MYSQL will upgrade or do some ammencements from time to time. Who knows? RAND() may be have it's own limitation in the future.

BTW, has CSN find the solution?

Thanx for replying. I'm eagerly looking for the answer.

amcgrath

Yea, as an example, I've seen this done in the past this way:

ad table hs ad campiagn stuff (banner and the like). A weight is assigned to the campaign within a range (say 1-10).

In a weight table there would be X amount of entries based on the weight chosen. Thene select a random on from the table. Not very elegant, but....

There are other more complicated ways to achieve this, especially if you factor in promised page views, etc and so on. Let me know if that helps.