When users have to select a random record from MySQL database, RAND() function may be used. The following statement displays a record chosen randomly from the database:

SELECT * FROM table ORDER BY RAND()  LIMIT 1;

Where,

  • ORDER BY sorts the table records, and
  • LIMIT 1 picks only one record from the result.

If 'N' rows have to be selected, the syntax above has to be modified to:

SELECT * FROM table ORDER BY RAND() LIMIT N;

However, this method is good for small databases as it has to create temporary table with result sets and pick a row randomly among them. One of the solutions for a larger database is picking up a random number from the ID column which has numbers ranging from 1...N and pull the data of the ID.

SET @ID = FLOOR(RAND()* N) + 1;

SELECT * FROM tblmedicationsdosages WHERE `CatID` < @ID LIMIT 2;

Here, a random number's 'ID' selected between 0-1 and multiplied by the range N. The whole number is obtained using FLOOR function. Then SELECT statement is used to select rows whose 'CatID' is smaller than the range set in 'ID'. This works faster as whole table need not be sorted.