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?
bergsyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gindeCommented:
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
0
BrianWrenCommented:
----------------------------------------------------------------------

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
0
bergsyAuthor Commented:
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' :)
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

BrianWrenCommented:
Dim rs as recordset
Din d as database
Dim TopNum as Long
Dim BotNum As Long
Dim RndNum As Long

' Get the highest and lowest values.
TopNum = DMax("[ID], "tblName")
BotNum = DMin("[ID], "tblName")


' Generate some percentage of the span.
RndNum = (TopNum - BotNum) * Rnd
' TopNum - BotNum = range

' Add that 'percentage' to the lowest #
RndNum = RndNum += BitNum


' Get a record
Set d = CurrentDB
Set rs = d.OpenRecordset( _
   "Select TOP 1 From tblName " & _
   "WHERE [ID] >= " & RndNum

Brian
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bergsyAuthor Commented:
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;
0
bergsyAuthor Commented:
Only average, as it didn't completely answer the question!
0
msowdersCommented:
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];
0
mastermaatCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.