Posted by J.O. Aho on 01/10/06 16:50

Stefan Rybacki wrote:
> J.O. Aho wrote:

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

Yes, it does take different entries, as a range of results is "locked" to one
name.



> Maybe I got your procedure wrong, but let me go thru it:
>
> Say we have the following table:
>
> +---------+-------+
> | name | rated | Range
> +---------+-------+
> | Thomson | 500 | 1-500
> | Milla | 10 | 501-510
> | Jensen | 1 | 511
> | Mike | 10 | 512-521
> +---------+-------+

Added the range, it's not a column, but showing what the "Rand()" should
generate to one person to be picked. As the SUM(rated) in this new table is
521, we generate a number between 1 and 521.



> I'm also not sure when the result is set? Never mind.

There are two lines that has to do with result, the first is when we define
the cursor cusro1

DECLARE curs1 CURSOR FOR SELECT Rated_Sum,Name FROM RateTable;

Which really just says select "rated" and "name" from the table.
If we just make "SELECT Rated_Sum,Name FROM RateTable;" in the sql, we would
get the whole table, as we have this as a cursor, we will step through the
table one row at the time, from the top to bottom.

The second that affects the result and really sets the value is

FETCH curs1 INTO temp,result;

Now we fetch the next line in the table, assign the "rated" value to the
variable 'temp' and the "name" to the variable 'result'.



> How do I ever get Jensen or Mike with your procedure?

To get Jensen or Mike, we need the SQL server to generate
CEIL(RAND(NOW())*SUM(Rated_Sum)) >= 511


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

Ordering wouldn't really make any difference, more than which "range" each
user will get

We have this starter table, say we have got CEIL(RAND(NOW())*SUM(Rated_Sum))==512

+---------+-------+
| name | rated | Range
+---------+-------+
| Thomson | 500 | 1-500
| Milla | 10 | 501-510
| Jensen | 1 | 511
| Mike | 10 | 512-521 *
+---------+-------+

Which would make Mike the "winner"

Let assume we use your rand and sort

+---------+-------+
| name | rated | Rand() Range
+---------+-------+
| Thomson | 500 | 0.769 1-500
| Mike | 10 | 0.905 501-510
| Milla | 10 | 0.704 511-520 *
| Jensen | 1 | 0.465 521
+---------+-------+

This case we would get Milla to be picked

The only major difference this will make is that you spend CPU cycles in
sorting the table, which is quite meaningless, as the order in the table won't
affect the how likely it's to be picked, just which value range you will get
for each name, as it's still as likely that you will generate the random
number 1 as 512.


I hope this explains enough for you to see how the procedure works.



//Aho

[Back to original message]