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?
-------------------------- ---------- ---------- ---------- ---------- ----
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
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
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' :)
Maybe someone could post some 'bad code' :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
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;
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 ];
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
$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
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
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