From: Peter Brawley
Date: February 21 2006 7:42pm
Subject: Re: Different result with subquery
List-Archive: http://lists.mysql.com/mysql/195026
Message-Id: <43FB6D28.6080906@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Neroux,

RAND() gives a roughly rectangular distribution, not a normal 
distribution, so samples of fewer than 100 or so values from it are 
likely to show large but statistically _insignificant_ differences.  In 
larger samples, I don't see any tendency of RAND() to produce more 
values near its mean (.5) than near its limits (0,1). You can check this 
yourself by populating a table with RAND() values then running a simple 
crosstab query eg

SELECT
  SUM(IF(r<0.1,1,0)) AS P1,
  SUM(IF(r>=0.1 AND r<0.2,1,0)) AS P2,
  SUM(IF(r>=0.2 AND r<0.3,1,0)) AS P3,
  SUM(IF(r>=0.3 AND r<0.4,1,0)) AS P4,
  SUM(IF(r>=0.4 AND r<0.5,1,0)) AS P5,
  SUM(IF(r>=0.5 AND r<0.6,1,0)) AS P6,
  SUM(IF(r>=0.6 AND r<0.7,1,0)) AS P7,
  SUM(IF(r>=0.7 AND r<0.8,1,0)) AS P8,
  SUM(IF(r>=0.8 AND r<0.9,1,0)) AS P9,
  SUM(IF(r>=0.9 AND r<1.0,1,0)) AS P10
FROM randresults;

PB

-----

neroux wrote:
> Hello,
>
> I am having a table with an integer column which I use for probability
> calculations (the higher a value the more often it is chosen).
>
> Now I am having the following query, which should actually incorporate
> these probabilities, however it seems to prefer values from the middle
> range
>
>   SELECT * FROM table WHERE field>=(SELECT RAND()*MAX(field) FROM
> table) ORDER BY field LIMIT 1
>
> If I split it up it still seems to prefer mid-range values over values
> closer to the higher end, however it doesnt "completely" ignore the
> highest value anymore.
>
>   random_value = SELECT RAND()*MAX(field) FROM table;
>   SELECT * FROM table WHERE field>=random_value ORDER BY field LIMIT 1
>
> Does anyone have an explanation for that or a better solution? Thanks
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com 
>
>   


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.11/264 - Release Date: 2/17/2006