?
Solved

How to select a row at random in MySQL

Posted on 1999-12-15
8
Medium Priority
?
760 Views
Last Modified: 2008-03-10
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?
0
Comment
Question by:bergsy
8 Comments
 
LVL 2

Expert Comment

by:ginde
ID: 2284372
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
 
LVL 9

Expert Comment

by:BrianWren
ID: 2284811
----------------------------------------------------------------------

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
 

Author Comment

by:bergsy
ID: 2285999
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 9

Accepted Solution

by:
BrianWren earned 100 total points
ID: 2288452
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
 

Author Comment

by:bergsy
ID: 2289044
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
 

Author Comment

by:bergsy
ID: 2289046
Only average, as it didn't completely answer the question!
0
 

Expert Comment

by:msowders
ID: 7759270
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
 

Expert Comment

by:mastermaat
ID: 7844766
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question