randomly picking a field 1

[ankursaxena]

How do i write a program which will pick a random record set from a table? i have a table of available numbers and i want to pick one with a where clause stating
where Status='Avail';
?? so that i get a random available telephone number to be assigned?

please help thanx a lot

Ankur

 

[petersJazz]

I dont think there is a statement for that.

You can do like this:

x = get a random number in your number interval

select *
from table
where number > x
order by number;

use the first line selected

 

[KarveR]

If you have an id field in your table (primary index auto-increment) you would use

SELECT @random:=truncate((rand() * 1000),0),phone_number FROM Table WHERE id = @random;


*note the * 1000 = 1000 records put here your number of records.

***************************************
Party on, dudes!

 

[KarveR]

Woops quick mod, currently you'll never be able to get your highest record so we need to add 1 to the equation.

SELECT @random:=truncate((rand() * 1000 +1 ),0),phone_number FROM Table WHERE id = @random; ***************************************
Party on, dudes!