List:General Discussion« Previous MessageNext Message »
From:neroux Date:February 21 2006 5:42pm
Subject:Different result with subquery
View as plain text  

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

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