Link to home
Start Free TrialLog in
Avatar of bergsy
bergsy

asked on

How to select a row at random in MySQL

Is it possible to select a row at random in MySQL. If not, is it possible to select a row by it's position in the table?
Avatar of ginde
ginde

1. You can't select a row at random. You can generate a random number from your front end and then access that row.
2. You have to have the (primary ) increamental key on that table and then you can go to this record using where clause, e.g. select * from table_A where id = 10
----------------------------------------------------------------------

Bad code often selects rows at random . . .  :o)


You could get the total # of rows, and then loop through the record set, going back to the begining if you hit the end early, and then after a random amount of time stop, and use that record.  The random primary key is good too theough, as long as you verify that your randomly generated key is a valid key.

Brian
Avatar of bergsy

ASKER

I have a primary key which is incremental. The only problem is sometimes rows get deleted. I could do a query and select all the rows, then pick a random row from the result, but it seems very inefficient. My front end is PHP by the way.

Maybe someone could post some 'bad code' :)
ASKER CERTIFIED SOLUTION
Avatar of BrianWren
BrianWren

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bergsy

ASKER

Thanks Brian,
Whilst not exactly answering the question, you pointed me in the right direction!
The 'TOP 1' part is absent from MySQL, but I looked at the fine manual & found a similar clause, 'LIMIT' which allows you to pick any row from a result set.
So, I can generate a random number ($rand) in PHP, then use something like this:-

SELECT * from mytable,
WHERE id=>$rand
LIMIT 1,1;
Avatar of bergsy

ASKER

Only average, as it didn't completely answer the question!
What you can do also is load a recordset into an array and use a random number as the index. That way your primary key does not have to be a number (for instance a product id like ABCD12345).
IN PHP:
$query = "SELECT * FROM table";
$result = mysql_query($query);
$productInfo = mysql_fetch_array($result);
$numberOfResults = mysql_num_rows($result);
$randomNumber = mt_rand(0,$numberOfResults-1);
$randomProduct = $productInfo[$randomNumber];
Hi bergsy,

I always thought that you could select a random row with the following query:

"SELECT * FROM tablename ORDER BY RAND() LIMIT 0,1"

You can also select more rows or add a WHERE-statement.

MasterMaat