Help with a weighted MySQL query

[Philosopher]

I have a table consisiting of up to 5 records with a field in the record that is a weight 0-10. 0 means don't show it, 10 means show it the most.

I'm trying to find a good way to get the weighting to work properly. I tried using a query such as

select * from TABLE where weight > 0 order BY RAND(),weight DESC

But that doesn't really give me the weighting I want. With this method, even if I give 1 a weight of 10 and the rest 1, the distribution is too even. Giving 1 a weight of 10 and the rest 1's I would expect the 10 weighted record to be pulled MUCH more often than any other record, but in my tests, that is not the case.

I've never messed with weighting before and was hoping someone might have a quick solution that would give give a more accurate reflection of their weights.

[mcibor]

this query means, that at first to each row there is assigned a random number, and the list is ordered by that. Next you check all the random numbers and if there are two that are the same they are being ordered by weight. however there are many many random numbers. What you need is to limit the randomization to two, three numbers generated and then order by that.

SELECT * FROM TABLE WHERE weight > 0 ORDER BY ROUND(RAND()), weight DESC;

Hope this solves the problem.
Best regards
Michal Cibor

[Philosopher]

Yes, that's what I realized.

I'm modifying some coding I had done for me on a larger project and getting a few things working exactly the way I want them.

Your solution definitely gets me closer to what I'm after. Not sure why I hadn't thought of that.

While it's a better solution, it still isn't the true weighting I'm wanting.

Does anyone have any other ideas that would get closer to a true weighting on a 1-10 scale?