|
|
Posted by J.O. Aho on 01/10/06 14:09
Stefan Rybacki wrote:
> J.O. Aho wrote:
>>> Joe wrote:
>>>
>>> ...
>>>
>>> ...
>>>
>>> -- Next line is first in the procedure
>>> DROP PROCEDURE IF EXISTS randrow;
>>> delimiter //
>>> CREATE PROCEDURE randrow(OUT result CHAR(20))
>>> BEGIN
>>> DECLARE valueadd INT DEFAULT 0;
>>> DECLARE rowsno INT DEFAULT 0;
>>> DECLARE temp INT DEFAULT 0;
>>> DECLARE curs1 CURSOR FOR SELECT Rated_Sum,Name FROM RateTable;
>>> SELECT @randvalue:=CEIL(RAND(NOW())*SUM(Rated_Sum)) AS RandNo FROM RateTable;
>>> OPEN curs1;
>>> REPEAT
>>> FETCH curs1 INTO temp,result;
>>> SET valueadd := valueadd + temp;
>>> UNTIL (@randvalue <= valueadd) END REPEAT;
>>> CLOSE curs1;
>>> END
>>> //
>>> delimiter ;
>>> -- previous line was the last line
>>>
>
> What if there are multiple entries with the same rated_sum value? As I
> understand right, you're just giving back the first found entry.
There aren't any less chance if the entry with the same value would be in the
end of the list or on the top of the list, all entries with the same Rated_Sum
will have the same chance to be picked.
SUM(Rated_Sum) gives the total of all Rated_Sum in the whole table and you
then generate a random number between 1 and the SUM(Rated_Sum), which makes
that if you have two entires with Rated_Sum=50, they have exactly the same
chance to be picked, regardless where in the list they do appear.
It's easy to try this out, just pick a 6 sided dice and start rolling and you
will see that it's as easy to get '1' as it's to get '6'.
The loop through the table is there to determine who is the "owner" of the
value, it's kind of doing as the modified table in Jonathan's post:
Name Rated_Sum
----------------------
Thompson 500
Milla 510
Jensen 511
//Aho
Navigation:
[Reply to this message]
|