Problem getting information out of a table

[NLCJ]

Hello,

I was trying to get a random value from the MySQL where one value is less than the other one. I've got this (so far).

SELECT * FROM table WHERE status='1' AND value1<value2 ORDER BY rand()

Value 1 is a variable from the same table as where it should get a random row from, the same counts for value2. I'm kinda stuck here...

 

Regards,

NLCJ

[andrewgauger]

Well what you quoted will compare value1 to value2 on the same row. 

 

ORDER BY rand() does not scale well.  What you should to is

$sql="SELECT count(*) as upper_limit FROM table WHERE status='1' AND value1<value2";
//todo: execute $sql... $result=mysql_fetch_assoc($sqlresult)
$within_range=rand(1,$result['upper_limit']);
$sql="SELECT * FROM table WHERE status='1' AND value1<value2 LIMIT $within_range , 1";
//todo execute $sql

[Ken2k7]

SELECT * FROM table WHERE status='1' AND value1<value2 ORDER BY rand() LIMIT 1;

[NLCJ]

Thank you, it's working.