Order By help

[atokatim]

I am attempting to figure out how to show random results, but display the highest value more often. I can't quite figure it out though 🙁

this is as far as i have got.

$sql = mysql_query("SELECT * FROM affiliates ORDER BY RAND(), referred DESC LIMIT 10");
while($result = mysql_fetch_object($sql)){
  echo $result->referred."<br>";
}

Thanks in advance!

[tomhath]

What you have will pretty much always sort by the random number.

Maybe multiply the random number by the "referred" value to bias it?

[Aybabtu]

Do you have to give a column name in the rand() function? Like rand(table_column_name). Just a thought.

[Roger_Ramjet]

Why do so many people want to do this?? Basically if you come to using RAND() in a query you are running frequently then your whole architecture is wrong and you should have planned for it from the start. 🆒

ORDER BY RAND() does NOT require a column name Aybabtu. It is also very heavy on the server: first it has to select all the rows that match your conditions, then it has to randomise them ALL, then it applies your LIMIT and returns your results.

A better way to do this is to select @ 1000 in descending order and then do it in php

$sql = mysql_query("SELECT * FROM affiliates ORDER BY referred DESC LIMIT 100"); 
$result = mysql_query($sql);
$j = rand(1,10)  // get random integer between 1 and 10
for ($i = 1; $i <= 10; $i++) {
   $row = $mysql_result($result, $j);
     // do what you have to with it
   $j = $j + rand(1,10);   // now increment it for a random series
}

So you would be jumping through the results randomly but hitting the highest referrers most often.
If you have a lot of referers then either adjust the rand scale or reduce it and multiply.