Find random stuff

Hi
I need some help doing a easy php/sql solution to a random selection with
rated stuff.
I have a table like:

Name Rated
--------------
Thompsen 500
Milla 10
Jensen 1

Means that the name Thompsen appears 50 times as often as Milla and 500
times as often Jensen.

I now need some scripting that counts the total ratefactor like
SELECT sum(Rated) FROM Table
In this case 511
And then make a random value between 1 and 511 and then print out the name,
which in this case will be Thompsen 500 of 511 times (in avg)

I need a quick way to find a name based on the Rated factors, any help?

Re: Find random stuff

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

Joe wrote:
> Hi
> I need some help doing a easy php/sql solution to a random selection with
> rated stuff.
> I have a table like:
>
> Name Rated
> --------------
> Thompsen 500
> Milla 10
> Jensen 1
>
> Means that the name Thompsen appears 50 times as often as Milla and 500
> times as often Jensen.
>
> I now need some scripting that counts the total ratefactor like
> SELECT sum(Rated) FROM Table
> In this case 511
> And then make a random value between 1 and 511 and then print out the name,
> which in this case will be Thompsen 500 of 511 times (in avg)
>
> I need a quick way to find a name based on the Rated factors, any help?
>

1. get your ratefactor (SELECT sum(rated) FROM table)
2. create a random number between 1 and ratefactor in PHP
3. get the random item (SELECT name FROM table WHERE rated>=created_randomnumber
ORDER BY rated, rand() LIMIT 1)

Regards
Stefan






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

iD8DBQFDwDDyyeCLzp/JKjARAq59AKCejex+Pq1JmUDNW36HYUH6GEzklACg xshu
Q476HxNXNdZzwZAByq0pyNA=
=LzSK
-----END PGP SIGNATURE-----

Re: Find random stuff

"Stefan Rybacki" skrev i en meddelelse
news:42apniF1hejs7U1@individual.net...
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Joe wrote:
>> Hi
>> I need some help doing a easy php/sql solution to a random selection with
>> rated stuff.
>> I have a table like:
>>
>> Name Rated
>> --------------
>> Thompsen 500
>> Milla 10
>> Jensen 1
>>
>> Means that the name Thompsen appears 50 times as often as Milla and 500
>> times as often Jensen.
>>
>> I now need some scripting that counts the total ratefactor like
>> SELECT sum(Rated) FROM Table
>> In this case 511
>> And then make a random value between 1 and 511 and then print out the
>> name,
>> which in this case will be Thompsen 500 of 511 times (in avg)
>>
>> I need a quick way to find a name based on the Rated factors, any help?
>>
>
> 1. get your ratefactor (SELECT sum(rated) FROM table)
> 2. create a random number between 1 and ratefactor in PHP
> 3. get the random item (SELECT name FROM table WHERE
> rated>=created_randomnumber
> ORDER BY rated, rand() LIMIT 1)
>
> Regards
> Stefan
>

I think I need some explanation for this :-)

As I see it it will give equal chance of the three names if the random
number is 1, is that correct?

If that's correct it will not be 1/511 chance of the name Jensen to appear.

Re: Find random stuff

Joe wrote:
> "Stefan Rybacki" skrev i en meddelelse
> news:42apniF1hejs7U1@individual.net...
>
>>-----BEGIN PGP SIGNED MESSAGE-----
>>Hash: SHA1
>>
>>Joe wrote:
>>
>>>Hi
>>>I need some help doing a easy php/sql solution to a random selection with
>>>rated stuff.
>>>I have a table like:
>>>
>>>Name Rated
>>>--------------
>>>Thompsen 500
>>>Milla 10
>>>Jensen 1
>>>
>>>Means that the name Thompsen appears 50 times as often as Milla and 500
>>>times as often Jensen.
>>>
>>>I now need some scripting that counts the total ratefactor like
>>>SELECT sum(Rated) FROM Table
>>>In this case 511
>>>And then make a random value between 1 and 511 and then print out the
>>>name,
>>>which in this case will be Thompsen 500 of 511 times (in avg)
>>>
>>>I need a quick way to find a name based on the Rated factors, any help?
>>>
>>
>>1. get your ratefactor (SELECT sum(rated) FROM table)
>>2. create a random number between 1 and ratefactor in PHP
>>3. get the random item (SELECT name FROM table WHERE
>>rated>=created_randomnumber
>>ORDER BY rated, rand() LIMIT 1)
>>
>>Regards
>>Stefan
>>
>
>
> I think I need some explanation for this :-)
>
> As I see it it will give equal chance of the three names if the random
> number is 1, is that correct?
>
> If that's correct it will not be 1/511 chance of the name Jensen to appear.
>

This might be a case of simple misunderstanding at first I thought you
had a a table with 500 rows containing the name Thompsen, 10 rows
containing the name Milla and one row containing the name Milla. This
makes a total of 511 rows. Then selecting based on the rownumber would
give you your statistic distribution.

But after reading your post another time I think the solution is a bit
more complex... as the table has only three rows with each name
appearing only once in combination with a number indicating the change
on the total. This is a bit harder to solve than if you would really
have 511 rows containg the data.

I think you need to use some program logic (or a stored procedure) to
determine which name to display:

1. Make a table that sums the rated number for every row above including
it self e.g.:

Name Rated_Sum
----------------------
Thompson 500
Milla 510
Jensen 511

2. Generate a random number with the range from 1 to the maximum value
(the value in the last row of the table) to determin something like the
row index in the example

3. Use logic to see if the random number is smaller or equal to the
Rated_Sum value of its own value and greater than the value in the last
row before its own value, e.g.:

The random number would be in the range of 1 up to and including 500
then you should return Thompson, but if the random number is greater
than 510 and less than or equal to 511 (only 511 would fit this
condition, assuming that you generate integer random numbers) you have
to output Jensen.

It makes it all a bit complicated... it might be easier to create a
temporary table holding the number of rows with the rated value and go
with the example of Stephan.

Good luck!

Jonathan

Re: Find random stuff

Joe wrote:
> "Stefan Rybacki" skrev i en meddelelse
> news:42apniF1hejs7U1@individual.net...
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> Joe wrote:
>>> Hi
>>> I need some help doing a easy php/sql solution to a random selection with
>>> rated stuff.
>>> I have a table like:
>>>
>>> Name Rated
>>> --------------
>>> Thompsen 500
>>> Milla 10
>>> Jensen 1
>>>
>>> Means that the name Thompsen appears 50 times as often as Milla and 500
>>> times as often Jensen.
>>>
>>> I now need some scripting that counts the total ratefactor like
>>> SELECT sum(Rated) FROM Table
>>> In this case 511
>>> And then make a random value between 1 and 511 and then print out the
>>> name,
>>> which in this case will be Thompsen 500 of 511 times (in avg)
>>>
>>> I need a quick way to find a name based on the Rated factors, any help?
>>>
>> 1. get your ratefactor (SELECT sum(rated) FROM table)
>> 2. create a random number between 1 and ratefactor in PHP
>> 3. get the random item (SELECT name FROM table WHERE
>> rated>=created_randomnumber
>> ORDER BY rated, rand() LIMIT 1)
>>
>> Regards
>> Stefan
>>
>
> I think I need some explanation for this :-)
>
> As I see it it will give equal chance of the three names if the random
> number is 1, is that correct?
>
> If that's correct it will not be 1/511 chance of the name Jensen to appear.
>
>
Think you have to use PROCEDURE to be able to do this completely in the SQL,
other wise you could just load everything into the PHP and process it in the
script, of course not the most optimal if there is a lot of rows in the database.
For PROCEDURE in MySQL you need version 5.0 or later.


//Aho

Re: Find random stuff

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

Joe wrote:
> "Stefan Rybacki" skrev i en meddelelse
> news:42apniF1hejs7U1@individual.net...
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> Joe wrote:
>>> Hi
>>> I need some help doing a easy php/sql solution to a random selection with
>>> rated stuff.
>>> I have a table like:
>>>
>>> Name Rated
>>> --------------
>>> Thompsen 500
>>> Milla 10
>>> Jensen 1
>>>
>>> Means that the name Thompsen appears 50 times as often as Milla and 500
>>> times as often Jensen.
>>>
>>> I now need some scripting that counts the total ratefactor like
>>> SELECT sum(Rated) FROM Table
>>> In this case 511
>>> And then make a random value between 1 and 511 and then print out the
>>> name,
>>> which in this case will be Thompsen 500 of 511 times (in avg)
>>>
>>> I need a quick way to find a name based on the Rated factors, any help?
>>>
>> 1. get your ratefactor (SELECT sum(rated) FROM table)
>> 2. create a random number between 1 and ratefactor in PHP
>> 3. get the random item (SELECT name FROM table WHERE
>> rated>=created_randomnumber
>> ORDER BY rated, rand() LIMIT 1)
>>
>> Regards
>> Stefan
>>
>
> I think I need some explanation for this :-)
>
> As I see it it will give equal chance of the three names if the random
> number is 1, is that correct?

no it doesn't. Lets go thru it.

1. the ratefactor is 511, ok to work with my query you just need the max rated
value so it is 500
2. you said your random number is 1
3. we're doing the following query:

SELECT name FROM table WHERE rated>=1
ORDER BY rated, rand() LIMIT 1

What we get is always Jensen. Why?
Lets remove the LIMIT 1 and lets also print out the rated column and lets see
the result:

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

lets say we have the random number 2 the result will look like this:

+---------+-------+
| name | rated |
+---------+-------+
| Milla | 10 |
| Thomson | 500 |
+---------+-------+

lets say we have the random number 11 the result is:

+---------+-------+
| name | rated |
+---------+-------+
| Thomson | 500 |
+---------+-------+

So as you can see, for any random number between <=1 you will get Jensen, for
any random number 2<= random number <=10 you'll get Milla as result and for any
random number greater than 10 you'll get Thomson.

By the way with MySQL 4.1 and higher you can do all this in just one query!

SELECT name, rated FROM table WHERE rated>=ROUND(rand()*(SELECT max(rated) FROM
table))
ORDER BY rated, rand() LIMIT 1

>
> If that's correct it will not be 1/511 chance of the name Jensen to appear.
>
>

Not really but almost its 1/500, if you want 1/511 you have to map all your
items to an interval for example 0 to 1 with respect to your rated value.

In this example I'm mapping the values to the interval of 0 to ratefactor (in
your example 0 to 511)

And with MySQL 4.1 and higher the following query can solve this problem:

SELECT name FROM `table` t2 WHERE
(SELECT sum(t1.rated) FROM `table` t1 WHERE t1.rated<=t2.rated)>=rand()*
(SELECT sum(rated) FROM `table`) ORDER BY rated, rand() LIMIT 1

I've the following table:

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

and on 521 runs I get the following distribution:

Run 1:

Thomson: 485
Milla: 20
Mike: 16
Jensen: 0

Run 2:

Thomson: 461
Milla: 27
Mike: 31
Jensen: 2

Run 3:

Thomson: 479
Milla: 22
Mike: 19
Jensen: 1

And that is pretty much what you're expecting right?

Unfortunately without subqueries you have to code it in your application.

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

iD8DBQFDwRkuyeCLzp/JKjARAjCXAJ0bczpC0wbTJLwd43QunKnRpVZmogCe JaVU
Wfjyuc6/Ilf9YG3XPju4Qmg=
=N3TE
-----END PGP SIGNATURE-----

Re: Find random stuff

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

Stefan Rybacki wrote:
> Joe wrote:
> ...
> no it doesn't. Lets go thru it.
>
>...
>
> By the way with MySQL 4.1 and higher you can do all this in just one query!
>
> SELECT name, rated FROM table WHERE rated>=ROUND(rand()*(SELECT max(rated) FROM
> table))
> ORDER BY rated, rand() LIMIT 1

I'm sorry but I found an error in this and the other 4.1 query.

You have to replace rand() by an already chosen random number or by a before set
user variable in mySQL otherwise you get wrong distributions (do not replace the
rand() in the order by clause!!!).

Example for the one above:

SET @random:=rand();
SELECT name, rated FROM table WHERE rated>=ROUND(@random*
(SELECT max(rated) FROM table))
ORDER BY rated, rand() LIMIT 1


>
>>> If that's correct it will not be 1/511 chance of the name Jensen to appear.
>>>
>>>
>
> Not really but almost its 1/500, if you want 1/511 you have to map all your
> items to an interval for example 0 to 1 with respect to your rated value.
>
> In this example I'm mapping the values to the interval of 0 to ratefactor (in
> your example 0 to 511)
>
> And with MySQL 4.1 and higher the following query can solve this problem:
>
> SELECT name FROM `table` t2 WHERE
> (SELECT sum(t1.rated) FROM `table` t1 WHERE t1.rated<=t2.rated)>=rand()*
> (SELECT sum(rated) FROM `table`) ORDER BY rated, rand() LIMIT 1
>

The same error here:

SET @random:=rand();
SELECT name FROM `table` t2 WHERE
(SELECT sum(t1.rated) FROM `table` t1 WHERE t1.rated<=t2.rated)>=@random*
(SELECT sum(rated) FROM `table`) ORDER BY rated, rand() LIMIT 1


> I've the following table:
>
> +---------+-------+
> | name | rated |
> +---------+-------+
> | Thomson | 500 |
> | Milla | 10 |
> | Jensen | 1 |
> | Mike | 10 |
> +---------+-------+
>
> and on 521 runs I get the following distribution:
>

So with the table above and another four runs I got the following results:

Thomson: 492
Milla: 14
Mike: 11
Jensen: 4

Thomson: 500
Milla: 9
Mike: 10
Jensen: 2

Thomson: 497
Milla: 15
Mike: 9
Jensen: 0

Thomson: 493
Milla: 16
Mike: 11
Jensen: 1

Regards
Stefan

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

iD8DBQFDwSGuyeCLzp/JKjARAmscAJ4kTtibl+n1JWZAnv68gvRHFGjASACg oZ0b
bVtwCQtjQx7c4X8t/xTN9Dk=
=I6fM
-----END PGP SIGNATURE-----

Re: Find random stuff

Joe wrote:

> I need some help doing a easy php/sql solution to a random selection with
> rated stuff.
> I have a table like:
>
> Name Rated
> --------------
> Thompsen 500
> Milla 10
> Jensen 1
>
> Means that the name Thompsen appears 50 times as often as Milla and 500
> times as often Jensen.
>
> I now need some scripting that counts the total ratefactor like
> SELECT sum(Rated) FROM Table
> In this case 511
> And then make a random value between 1 and 511 and then print out the name,
> which in this case will be Thompsen 500 of 511 times (in avg)
>
> I need a quick way to find a name based on the Rated factors, any help?


Much depends on what sql server you are using and what version, if you have a
sql server that supports procedures (mysql 5), then you can create a procedure
that will fetch the result for you.

I have made a procedure called randrow which will check whats the total of all
Rated_Sum in the table and then use the RAND() to make a random value. In your
original table you had SUM(Rated_Sum)=511, take that times the RAND() and then
CEIL() the result, you should get a value between 1 and 511 (if you want 0 to
511 then use ROUND() instead).

After this we will loop through the table until the added values of Rated_Sum
are equal or the same as the random value we generated and we will return this
users name.

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

This we can do once directly in the database where we want to use this procedure.

From the PHP we will now need to call two SQL statements, the first to call
the procedure we made and store the return value in a variable which we will fetch

CALL randrow(@a);
SELECT @a;

I guess this will be one of the faster ways to do the whole thing, as if you
do it on the php side, you will have to fetch the whole table and if the web
server where you run the script is another one than the one that runs the
database, then you may notice slowness as the whole table has to be
transported over the network or in worst case over internet.
With the procedure method, only the result would be transported, something
that maybe is as much as 80 bytes long compared with 5000 row with each upto
85 bytes, which would be around 415kb in worst case.



//Aho

Re: Find random stuff

"Jonathan" skrev i en meddelelse
news:43c0d064$0$10081$ba620dc5@text.nova.planet.nl...
> Joe wrote:
>> "Stefan Rybacki" skrev i en meddelelse
>> news:42apniF1hejs7U1@individual.net...
>>
>>>-----BEGIN PGP SIGNED MESSAGE-----
>>>Hash: SHA1
>>>
>>>Joe wrote:
>>>
>>>>Hi
>>>>I need some help doing a easy php/sql solution to a random selection
>>>>with
>>>>rated stuff.
>>>>I have a table like:
>>>>
>>>>Name Rated
>>>>--------------
>>>>Thompsen 500
>>>>Milla 10
>>>>Jensen 1
>>>>
>>>>Means that the name Thompsen appears 50 times as often as Milla and 500
>>>>times as often Jensen.
>>>>
>>>>I now need some scripting that counts the total ratefactor like
>>>>SELECT sum(Rated) FROM Table
>>>>In this case 511
>>>>And then make a random value between 1 and 511 and then print out the
>>>>name,
>>>>which in this case will be Thompsen 500 of 511 times (in avg)
>>>>
>>>>I need a quick way to find a name based on the Rated factors, any help?
>>>>
>>>
>>>1. get your ratefactor (SELECT sum(rated) FROM table)
>>>2. create a random number between 1 and ratefactor in PHP
>>>3. get the random item (SELECT name FROM table WHERE
>>>rated>=created_randomnumber
>>>ORDER BY rated, rand() LIMIT 1)
>>>
>>>Regards
>>>Stefan
>>>
>>
>>
>> I think I need some explanation for this :-)
>>
>> As I see it it will give equal chance of the three names if the random
>> number is 1, is that correct?
>>
>> If that's correct it will not be 1/511 chance of the name Jensen to
>> appear.
>>
>
> This might be a case of simple misunderstanding at first I thought you had
> a a table with 500 rows containing the name Thompsen, 10 rows containing
> the name Milla and one row containing the name Milla. This makes a total
> of 511 rows. Then selecting based on the rownumber would give you your
> statistic distribution.
>
> But after reading your post another time I think the solution is a bit
> more complex... as the table has only three rows with each name appearing
> only once in combination with a number indicating the change on the total.
> This is a bit harder to solve than if you would really have 511 rows
> containg the data.
>
> I think you need to use some program logic (or a stored procedure) to
> determine which name to display:
>
> 1. Make a table that sums the rated number for every row above including
> it self e.g.:
>
> Name Rated_Sum
> ----------------------
> Thompson 500
> Milla 510
> Jensen 511
>
> 2. Generate a random number with the range from 1 to the maximum value
> (the value in the last row of the table) to determin something like the
> row index in the example
>
> 3. Use logic to see if the random number is smaller or equal to the
> Rated_Sum value of its own value and greater than the value in the last
> row before its own value, e.g.:
>
> The random number would be in the range of 1 up to and including 500 then
> you should return Thompson, but if the random number is greater than 510
> and less than or equal to 511 (only 511 would fit this condition, assuming
> that you generate integer random numbers) you have to output Jensen.
>
> It makes it all a bit complicated... it might be easier to create a
> temporary table holding the number of rows with the rated value and go
> with the example of Stephan.
>
> Good luck!
>
> Jonathan

That's correct Jonathan, it's 3 rows not 511

Re: Find random stuff

"Stefan Rybacki" skrev i en meddelelse
news:42cltdF1i3ad8U1@individual.net...
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Stefan Rybacki wrote:
>> Joe wrote:
>> ...
>> no it doesn't. Lets go thru it.
>>
>>...
>>
>> By the way with MySQL 4.1 and higher you can do all this in just one
>> query!
>>
>> SELECT name, rated FROM table WHERE rated>=ROUND(rand()*(SELECT
>> max(rated) FROM
>> table))
>> ORDER BY rated, rand() LIMIT 1
>
> I'm sorry but I found an error in this and the other 4.1 query.
>
> You have to replace rand() by an already chosen random number or by a
> before set
> user variable in mySQL otherwise you get wrong distributions (do not
> replace the
> rand() in the order by clause!!!).
>
> Example for the one above:
>
> SET @random:=rand();
> SELECT name, rated FROM table WHERE rated>=ROUND(@random*
> (SELECT max(rated) FROM table))
> ORDER BY rated, rand() LIMIT 1
>
>
>>
>>>> If that's correct it will not be 1/511 chance of the name Jensen to
>>>> appear.
>>>>
>>>>
>>
>> Not really but almost its 1/500, if you want 1/511 you have to map all
>> your
>> items to an interval for example 0 to 1 with respect to your rated value.
>>
>> In this example I'm mapping the values to the interval of 0 to ratefactor
>> (in
>> your example 0 to 511)
>>
>> And with MySQL 4.1 and higher the following query can solve this problem:
>>
>> SELECT name FROM `table` t2 WHERE
>> (SELECT sum(t1.rated) FROM `table` t1 WHERE t1.rated<=t2.rated)>=rand()*
>> (SELECT sum(rated) FROM `table`) ORDER BY rated, rand() LIMIT 1
>>
>
> The same error here:
>
> SET @random:=rand();
> SELECT name FROM `table` t2 WHERE
> (SELECT sum(t1.rated) FROM `table` t1 WHERE t1.rated<=t2.rated)>=@random*
> (SELECT sum(rated) FROM `table`) ORDER BY rated, rand() LIMIT 1
>
>
>> I've the following table:
>>
>> +---------+-------+
>> | name | rated |
>> +---------+-------+
>> | Thomson | 500 |
>> | Milla | 10 |
>> | Jensen | 1 |
>> | Mike | 10 |
>> +---------+-------+
>>
>> and on 521 runs I get the following distribution:
>>
>
> So with the table above and another four runs I got the following results:
>
> Thomson: 492
> Milla: 14
> Mike: 11
> Jensen: 4
>
> Thomson: 500
> Milla: 9
> Mike: 10
> Jensen: 2
>
> Thomson: 497
> Milla: 15
> Mike: 9
> Jensen: 0
>
> Thomson: 493
> Milla: 16
> Mike: 11
> Jensen: 1
>
> Regards
> Stefan

It's a solution like this I'm looking for, I do however get errors when
doing this, can you help me again showing the SQL query and explain it?

Re: Find random stuff

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

Joe wrote:
> ...
>
> It's a solution like this I'm looking for, I do however get errors when
> doing this, can you help me again showing the SQL query and explain it?
>
>

Ok, first what MySQL version do you use?
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1rc2 (MingW32)

iD8DBQFDwYP5yeCLzp/JKjARAtuBAJ44vEZT+a0SnvtuZ0bLyivSFG1y9gCf QvMo
I9iaHobktIOkGYac/ngF8dc=
=SaTz
-----END PGP SIGNATURE-----

Re: Find random stuff

try looking in the MySQL manual for the CASE statement.
I think it might fit your needs for 3 rows. but if you have more than that,
you've got a different problem to solve.
MySQL also has a RAND() function which generates a FLOAT number between 0.0
and 1.0. you can't use it in an ORDER BY clause, because RAND() would
recalc multiple times. might be better to stuff it in a @VARIABLE first if
you can, like @V:=RAND();
ahh - maybe it's simpler doing it in PHP anyway. :-)

SELECT CASE WHERE condition THEN column WHERE condition THEN column ELSE
column END FROM tablename;
I think that's MySQL's version of CASE. SQL2 doesn't use END.

"Jonathan" wrote in message
news:43c0d064$0$10081$ba620dc5@text.nova.planet.nl...
> Joe wrote:
>> "Stefan Rybacki" skrev i en meddelelse
>> news:42apniF1hejs7U1@individual.net...
>>
>>>-----BEGIN PGP SIGNED MESSAGE-----
>>>Hash: SHA1
>>>
>>>Joe wrote:
>>>
>>>>Hi
>>>>I need some help doing a easy php/sql solution to a random selection
>>>>with
>>>>rated stuff.
>>>>I have a table like:
>>>>
>>>>Name Rated
>>>>--------------
>>>>Thompsen 500
>>>>Milla 10
>>>>Jensen 1
>>>>
>>>>Means that the name Thompsen appears 50 times as often as Milla and 500
>>>>times as often Jensen.
>>>>
>>>>I now need some scripting that counts the total ratefactor like
>>>>SELECT sum(Rated) FROM Table
>>>>In this case 511
>>>>And then make a random value between 1 and 511 and then print out the
>>>>name,
>>>>which in this case will be Thompsen 500 of 511 times (in avg)
>>>>
>>>>I need a quick way to find a name based on the Rated factors, any help?
>>>>
>>>
>>>1. get your ratefactor (SELECT sum(rated) FROM table)
>>>2. create a random number between 1 and ratefactor in PHP
>>>3. get the random item (SELECT name FROM table WHERE
>>>rated>=created_randomnumber
>>>ORDER BY rated, rand() LIMIT 1)
>>>
>>>Regards
>>>Stefan
>>>
>>
>>
>> I think I need some explanation for this :-)
>>
>> As I see it it will give equal chance of the three names if the random
>> number is 1, is that correct?
>>
>> If that's correct it will not be 1/511 chance of the name Jensen to
>> appear.
>>
>
> This might be a case of simple misunderstanding at first I thought you had
> a a table with 500 rows containing the name Thompsen, 10 rows containing
> the name Milla and one row containing the name Milla. This makes a total
> of 511 rows. Then selecting based on the rownumber would give you your
> statistic distribution.
>
> But after reading your post another time I think the solution is a bit
> more complex... as the table has only three rows with each name appearing
> only once in combination with a number indicating the change on the total.
> This is a bit harder to solve than if you would really have 511 rows
> containg the data.
>
> I think you need to use some program logic (or a stored procedure) to
> determine which name to display:
>
> 1. Make a table that sums the rated number for every row above including
> it self e.g.:
>
> Name Rated_Sum
> ----------------------
> Thompson 500
> Milla 510
> Jensen 511
>
> 2. Generate a random number with the range from 1 to the maximum value
> (the value in the last row of the table) to determin something like the
> row index in the example
>
> 3. Use logic to see if the random number is smaller or equal to the
> Rated_Sum value of its own value and greater than the value in the last
> row before its own value, e.g.:
>
> The random number would be in the range of 1 up to and including 500 then
> you should return Thompson, but if the random number is greater than 510
> and less than or equal to 511 (only 511 would fit this condition, assuming
> that you generate integer random numbers) you have to output Jensen.
>
> It makes it all a bit complicated... it might be easier to create a
> temporary table holding the number of rows with the rated value and go
> with the example of Stephan.
>
> Good luck!
>
> Jonathan

Re: Find random stuff

OOPS! I was wrong. you *can* use RAND() in an ORDER BY clause. the manual
states:
You can't use a column with RAND() values in an ORDER BY clause, because
ORDER BY would evaluate the column multiple times. As of MySQL 3.23, you can
retrieve rows in random order like this:


mysql> SELECT * FROM tbl_name ORDER BY RAND();

ORDER BY RAND() combined with LIMIT is useful for selecting a random sample
of a set of rows:


mysql> SELECT * FROM table1, table2 WHERE a=b AND c -> ORDER BY RAND() LIMIT 1000;

Note that RAND() in a WHERE clause is re-evaluated every time the WHERE is
executed.



"Jim Michaels" wrote in message
news:Lr6dnbzf_84baV_eRVn-gw@comcast.com...
> try looking in the MySQL manual for the CASE statement.
> I think it might fit your needs for 3 rows. but if you have more than
> that, you've got a different problem to solve.
> MySQL also has a RAND() function which generates a FLOAT number between
> 0.0 and 1.0. you can't use it in an ORDER BY clause, because RAND() would
> recalc multiple times. might be better to stuff it in a @VARIABLE first
> if you can, like @V:=RAND();
> ahh - maybe it's simpler doing it in PHP anyway. :-)
>
> SELECT CASE WHERE condition THEN column WHERE condition THEN column ELSE
> column END FROM tablename;
> I think that's MySQL's version of CASE. SQL2 doesn't use END.
>
> "Jonathan" wrote in message
> news:43c0d064$0$10081$ba620dc5@text.nova.planet.nl...
>> Joe wrote:
>>> "Stefan Rybacki" skrev i en meddelelse
>>> news:42apniF1hejs7U1@individual.net...
>>>
>>>>-----BEGIN PGP SIGNED MESSAGE-----
>>>>Hash: SHA1
>>>>
>>>>Joe wrote:
>>>>
>>>>>Hi
>>>>>I need some help doing a easy php/sql solution to a random selection
>>>>>with
>>>>>rated stuff.
>>>>>I have a table like:
>>>>>
>>>>>Name Rated
>>>>>--------------
>>>>>Thompsen 500
>>>>>Milla 10
>>>>>Jensen 1
>>>>>
>>>>>Means that the name Thompsen appears 50 times as often as Milla and 500
>>>>>times as often Jensen.
>>>>>
>>>>>I now need some scripting that counts the total ratefactor like
>>>>>SELECT sum(Rated) FROM Table
>>>>>In this case 511
>>>>>And then make a random value between 1 and 511 and then print out the
>>>>>name,
>>>>>which in this case will be Thompsen 500 of 511 times (in avg)
>>>>>
>>>>>I need a quick way to find a name based on the Rated factors, any help?
>>>>>
>>>>
>>>>1. get your ratefactor (SELECT sum(rated) FROM table)
>>>>2. create a random number between 1 and ratefactor in PHP
>>>>3. get the random item (SELECT name FROM table WHERE
>>>>rated>=created_randomnumber
>>>>ORDER BY rated, rand() LIMIT 1)
>>>>
>>>>Regards
>>>>Stefan
>>>>
>>>
>>>
>>> I think I need some explanation for this :-)
>>>
>>> As I see it it will give equal chance of the three names if the random
>>> number is 1, is that correct?
>>>
>>> If that's correct it will not be 1/511 chance of the name Jensen to
>>> appear.
>>>
>>
>> This might be a case of simple misunderstanding at first I thought you
>> had a a table with 500 rows containing the name Thompsen, 10 rows
>> containing the name Milla and one row containing the name Milla. This
>> makes a total of 511 rows. Then selecting based on the rownumber would
>> give you your statistic distribution.
>>
>> But after reading your post another time I think the solution is a bit
>> more complex... as the table has only three rows with each name appearing
>> only once in combination with a number indicating the change on the
>> total. This is a bit harder to solve than if you would really have 511
>> rows containg the data.
>>
>> I think you need to use some program logic (or a stored procedure) to
>> determine which name to display:
>>
>> 1. Make a table that sums the rated number for every row above including
>> it self e.g.:
>>
>> Name Rated_Sum
>> ----------------------
>> Thompson 500
>> Milla 510
>> Jensen 511
>>
>> 2. Generate a random number with the range from 1 to the maximum value
>> (the value in the last row of the table) to determin something like the
>> row index in the example
>>
>> 3. Use logic to see if the random number is smaller or equal to the
>> Rated_Sum value of its own value and greater than the value in the last
>> row before its own value, e.g.:
>>
>> The random number would be in the range of 1 up to and including 500 then
>> you should return Thompson, but if the random number is greater than 510
>> and less than or equal to 511 (only 511 would fit this condition,
>> assuming that you generate integer random numbers) you have to output
>> Jensen.
>>
>> It makes it all a bit complicated... it might be easier to create a
>> temporary table holding the number of rows with the rated value and go
>> with the example of Stephan.
>>
>> Good luck!
>>
>> Jonathan
>
>

Re: Find random stuff

Jim Michaels wrote:
> OOPS! I was wrong. you *can* use RAND() in an ORDER BY clause. the manual
> states:
> You can't use a column with RAND() values in an ORDER BY clause, because
> ORDER BY would evaluate the column multiple times. As of MySQL 3.23, you can
> retrieve rows in random order like this:
>
>
> mysql> SELECT * FROM tbl_name ORDER BY RAND();
>
> ORDER BY RAND() combined with LIMIT is useful for selecting a random sample
> of a set of rows:
>
>
> mysql> SELECT * FROM table1, table2 WHERE a=b AND c > -> ORDER BY RAND() LIMIT 1000;
>
> Note that RAND() in a WHERE clause is re-evaluated every time the WHERE is
> executed.

This don't fit the OP, as instance with a higher Rated_Sum should be picked
more often than one with a low value, the way you suggest here would give each
row the same chance to be in the top of the list.

If wanting to get the results as in the first post, then procedure (mysql5) is
the only way to get it, without using help of PHP, if not be that strict, then
you can do it with subselects (mysql4) as Stefan suggested.


//Aho

Re: Find random stuff

You can't use a column with RAND() values in an ORDER BY clause, because
ORDER BY would evaluate the column multiple times. As of MySQL 3.23, you can
retrieve rows in random order like this:


mysql> SELECT * FROM tbl_name ORDER BY RAND();

ORDER BY RAND() combined with LIMIT is useful for selecting a random sample
of a set of rows:


mysql> SELECT * FROM table1, table2 WHERE a=b AND c -> ORDER BY RAND() LIMIT 1000;

Note that RAND() in a WHERE clause is re-evaluated every time the WHERE is
executed.

"Stefan Rybacki" wrote in message
news:42cltdF1i3ad8U1@individual.net...
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Stefan Rybacki wrote:
>> Joe wrote:
>> ...
>> no it doesn't. Lets go thru it.
>>
>>...
>>
>> By the way with MySQL 4.1 and higher you can do all this in just one
>> query!
>>
>> SELECT name, rated FROM table WHERE rated>=ROUND(rand()*(SELECT
>> max(rated) FROM
>> table))
>> ORDER BY rated, rand() LIMIT 1
>
> I'm sorry but I found an error in this and the other 4.1 query.
>
> You have to replace rand() by an already chosen random number or by a
> before set
> user variable in mySQL otherwise you get wrong distributions (do not
> replace the
> rand() in the order by clause!!!).
>
> Example for the one above:
>
> SET @random:=rand();
> SELECT name, rated FROM table WHERE rated>=ROUND(@random*
> (SELECT max(rated) FROM table))
> ORDER BY rated, rand() LIMIT 1
>
>
>>
>>>> If that's correct it will not be 1/511 chance of the name Jensen to
>>>> appear.
>>>>
>>>>
>>
>> Not really but almost its 1/500, if you want 1/511 you have to map all
>> your
>> items to an interval for example 0 to 1 with respect to your rated value.
>>
>> In this example I'm mapping the values to the interval of 0 to ratefactor
>> (in
>> your example 0 to 511)
>>
>> And with MySQL 4.1 and higher the following query can solve this problem:
>>
>> SELECT name FROM `table` t2 WHERE
>> (SELECT sum(t1.rated) FROM `table` t1 WHERE t1.rated<=t2.rated)>=rand()*
>> (SELECT sum(rated) FROM `table`) ORDER BY rated, rand() LIMIT 1
>>
>
> The same error here:
>
> SET @random:=rand();
> SELECT name FROM `table` t2 WHERE
> (SELECT sum(t1.rated) FROM `table` t1 WHERE t1.rated<=t2.rated)>=@random*
> (SELECT sum(rated) FROM `table`) ORDER BY rated, rand() LIMIT 1
>
>
>> I've the following table:
>>
>> +---------+-------+
>> | name | rated |
>> +---------+-------+
>> | Thomson | 500 |
>> | Milla | 10 |
>> | Jensen | 1 |
>> | Mike | 10 |
>> +---------+-------+
>>
>> and on 521 runs I get the following distribution:
>>
>
> So with the table above and another four runs I got the following results:
>
> Thomson: 492
> Milla: 14
> Mike: 11
> Jensen: 4
>
> Thomson: 500
> Milla: 9
> Mike: 10
> Jensen: 2
>
> Thomson: 497
> Milla: 15
> Mike: 9
> Jensen: 0
>
> Thomson: 493
> Milla: 16
> Mike: 11
> Jensen: 1
>
> Regards
> Stefan
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1rc2 (MingW32)
>
> iD8DBQFDwSGuyeCLzp/JKjARAmscAJ4kTtibl+n1JWZAnv68gvRHFGjASACg oZ0b
> bVtwCQtjQx7c4X8t/xTN9Dk=
> =I6fM
> -----END PGP SIGNATURE-----

Re: Find random stuff

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

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.

Regards
Stefan

>...
>
> //Aho

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

iD8DBQFDw5pByeCLzp/JKjARApKNAJ9i+3iEl9XueFBtnGUBq7myzDmlugCc C9lg
WWfDSR7zMGkLws6MbOTfjmg=
=8pH/
-----END PGP SIGNATURE-----

Re: Find random stuff

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

J.O. Aho wrote:
>...
> This don't fit the OP, as instance with a higher Rated_Sum should be picked
> more often than one with a low value, the way you suggest here would give each
> row the same chance to be in the top of the list.

Correct.

>
> If wanting to get the results as in the first post, then procedure (mysql5) is
> the only way to get it, without using help of PHP, if not be that strict, then
> you can do it with subselects (mysql4) as Stefan suggested.

What do you mean by strict. Don't I get the same distribution like your
procedure except the fact of multiple entries having the same rated value?

Regards
Stefan

>
>
> //Aho

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

iD8DBQFDw5qTyeCLzp/JKjARAn3SAJkBOmDXUEj11M7E0cCt5vdkowxOtgCb BNxt
tosLvZHOLY7NfUT73Ik7F1U=
=gfnL
-----END PGP SIGNATURE-----

Re: Find random stuff

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

Re: Find random stuff

Stefan Rybacki wrote:
> J.O. Aho wrote:
>>> ...
>>> This don't fit the OP, as instance with a higher Rated_Sum should be picked
>>> more often than one with a low value, the way you suggest here would give each
>>> row the same chance to be in the top of the list.
>
> Correct.
>
>>> If wanting to get the results as in the first post, then procedure (mysql5) is
>>> the only way to get it, without using help of PHP, if not be that strict, then
>>> you can do it with subselects (mysql4) as Stefan suggested.
>
> What do you mean by strict. Don't I get the same distribution like your
> procedure except the fact of multiple entries having the same rated value?

OP wanted to have 1/511 chance for a single number, while your example had
1/500 according to your own post. So 'strictly' would be a 1/511 chance.


//Aho

Re: Find random stuff

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

J.O. Aho wrote:
> Stefan Rybacki wrote:
>> J.O. Aho wrote:
>>>> ...
>>>> This don't fit the OP, as instance with a higher Rated_Sum should be picked
>>>> more often than one with a low value, the way you suggest here would give each
>>>> row the same chance to be in the top of the list.
>> Correct.
>>
>>>> If wanting to get the results as in the first post, then procedure (mysql5) is
>>>> the only way to get it, without using help of PHP, if not be that strict, then
>>>> you can do it with subselects (mysql4) as Stefan suggested.
>> What do you mean by strict. Don't I get the same distribution like your
>> procedure except the fact of multiple entries having the same rated value?
>
> OP wanted to have 1/511 chance for a single number, while your example had
> 1/500 according to your own post. So 'strictly' would be a 1/511 chance.
>

Not the second query, where I developed a query using subqueries which achives
right this. 1/511, 10/511 and 500/511

Regards
Stefan

>
> //Aho

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

iD8DBQFDw7wmyeCLzp/JKjARAgshAKDA4w6RGwJEHGdKITL1vmVFZ2vzcQCg xZcQ
ySML96p8la1PEd3Jb7mlT9o=
=svLP
-----END PGP SIGNATURE-----

Re: Find random stuff

-----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/pr0Ifo14ns5qImjORHxXX1GTgCg lTYw
jxxCG+i+GZg+E+YgM5HClgc=
=acOg
-----END PGP SIGNATURE-----

Re: Find random stuff

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

Re: Find random stuff

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

J.O. Aho wrote:
> Stefan Rybacki wrote:
>> J.O. Aho wrote:
> ...
>
> I hope this explains enough for you to see how the procedure works.
>

Yes thanks, you're right. I just didn't saw the obvious and thought to
complicated, sorry. ;)

>
>
> //Aho

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

iD8DBQFDw82byeCLzp/JKjARAoV4AJ9XLyGu5Nqta73j527Jio/hNaT/ZQCf Yayj
BY8S2agqmwfB2NIEwrYo2tw=
=esOB
-----END PGP SIGNATURE-----

Re: Find random stuff

Stefan Rybacki wrote:
> J.O. Aho wrote:
>>> Stefan Rybacki wrote:
>>>> J.O. Aho wrote:
>>> ...
>>>
>>> I hope this explains enough for you to see how the procedure works.
>>>
>
> Yes thanks, you're right. I just didn't saw the obvious and thought to
> complicated, sorry. ;)

Nothing to be sorry about, gosh I did fight with this procedure myself for
many hours, was doing the fetch loop with a while loop where I used limit to
decide the line from the database to get, but sadly (or luckily) limit don't
take variables as arguments, you have always give them constant values.

The main thing is that Joe gets his query to work, no matter if it's with
subselects or procedures.


//Aho

Re: Find random stuff

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

J.O. Aho wrote:
>...
> Nothing to be sorry about, gosh I did fight with this procedure myself for
> many hours, was doing the fetch loop with a while loop where I used limit to
> decide the line from the database to get, but sadly (or luckily) limit don't
> take variables as arguments, you have always give them constant values.
>
> The main thing is that Joe gets his query to work, no matter if it's with
> subselects or procedures.

Yep, but he never told us whether he got it to work or not. Well may be future
will tell. ;)

Regards
Stefan

>
>
> //Aho

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

iD8DBQFDw93oyeCLzp/JKjARAokiAJ4hwDzfVUj+3qX857Cq6xT7e99d6gCd H/mI
u3gjFjta2k9jxNd2rr3J5fM=
=nzqp
-----END PGP SIGNATURE-----

Re: Find random stuff

Hi
I mus admit I'm veru confused after reading all the answers, I tried all of
them but still it doesn't work, must be my knowledge that fails me even when
I have the answer :-(


"Stefan Rybacki" skrev i en meddelelse
news:42i4v7F1i00meU1@individual.net...
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> J.O. Aho wrote:
>>...
>> Nothing to be sorry about, gosh I did fight with this procedure myself
>> for
>> many hours, was doing the fetch loop with a while loop where I used limit
>> to
>> decide the line from the database to get, but sadly (or luckily) limit
>> don't
>> take variables as arguments, you have always give them constant values.
>>
>> The main thing is that Joe gets his query to work, no matter if it's with
>> subselects or procedures.
>
> Yep, but he never told us whether he got it to work or not. Well may be
> future
> will tell. ;)
>
> Regards
> Stefan
>
>>
>>
>> //Aho
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1rc2 (MingW32)
>
> iD8DBQFDw93oyeCLzp/JKjARAokiAJ4hwDzfVUj+3qX857Cq6xT7e99d6gCd H/mI
> u3gjFjta2k9jxNd2rr3J5fM=
> =nzqp
> -----END PGP SIGNATURE-----

Re: Find random stuff

Joe wrote:
> Hi
> I mus admit I'm veru confused after reading all the answers, I tried all of
> them but still it doesn't work, must be my knowledge that fails me even when
> I have the answer :-(

Lets start from the beginning, which version of MySQL did you use?
This is a quite important question to answer as this tells what you can and
can't do.


//Aho