From: neroux
Date: February 21 2006 5:42pm
Subject: Different result with subquery
List-Archive: http://lists.mysql.com/mysql/195018
Message-Id: <20060221174201.90492.qmail@web33404.mail.mud.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit


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