Posted by Stefan Rybacki on 01/10/06 16:03

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

J.O. Aho wrote:
> 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.

That is true.

>
> 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.
>

Yes.

> 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'.

I know.

>
> 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
>

But, what I meant is does your procedure choose different entires with the same
rated_sum?

Maybe I got your procedure wrong, but let me go thru it:

Say we have the following table:

+---------+-------+
| name | rated |
+---------+-------+
| Thomson | 500 |
| Milla | 10 |
| Jensen | 1 |
| Mike | 10 |
+---------+-------+

where rated is your rated_sum value

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;

say @randvalue=510

OPEN curs1;
REPEAT
FETCH curs1 INTO temp,result;
SET valueadd := valueadd + temp;
UNTIL (@randvalue <= valueadd) END REPEAT;

ok, 1. loop
temp=500, name=Thomson
valueadd=0+500;
510 <= 500 -> false

2. loop
temp=10, name=Milla
valueadd=500+10
510 <= 510 -> true

Return Milla

CLOSE curs1;
END

I'm also not sure when the result is set? Never mind. How do I ever get Jensen
or Mike with your procedure? I guess what you have to do is to add an ORDER BY
rated_sum DESC, rand() to the cursors select to get right results. If not I
would appreciate an explanation.

Regards
Stefan

>
>
>
> //Aho

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1rc2 (MingW32)

iD8DBQFDw77KyeCLzp/JKjARAjWDAJ45/pr0Ifo14ns5qImjORHxXX1GTgCglTYw
jxxCG+i+GZg+E+YgM5HClgc=
=acOg
-----END PGP SIGNATURE-----

[Back to original message]