List:General Discussion« Previous MessageNext Message »
From:neroux Date:February 22 2006 12:02am
Subject:Re: Different result with subquery
View as plain text  
Hello Peter,

thanks for your reply, however my question was less related to RAND()
but more about the weird situation that the results are more or less
fine with the two queries, however different when they are combined
into one query with a subquery.

Thanks

--- Peter Brawley <peter.brawley@stripped> wrote:

> 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 
Thread
Different result with subqueryneroux21 Feb
  • Re: Different result with subqueryPeter Brawley21 Feb
    • Re: Different result with subqueryneroux22 Feb