|
|
Posted by Jim Michaels on 01/10/06 02:49
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<d
-> ORDER BY RAND() LIMIT 1000;
Note that RAND() in a WHERE clause is re-evaluated every time the WHERE is
executed.
"Stefan Rybacki" <stefan.rybacki@gmx.net> 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+n1JWZAnv68gvRHFGjASACgoZ0b
> bVtwCQtjQx7c4X8t/xTN9Dk=
> =I6fM
> -----END PGP SIGNATURE-----
Navigation:
[Reply to this message]
|