Retrieving/selecting A Particular Number Of Random Rows From A Database Table

Hey Programmers on nairaland,

I'm working on a particular project and there is an SQL query I have been trying to put together. This particular task involves a situation in which I have to randomly select, say 50 rows from a particular MySQL database table. The thing is that the 50 rows selected at any point must be random and no row must be selected twice. Therefore, each time the select query is executed, it always returns 50 different rows. (This is of course assuming that the database table has over 50 rows, maybe like 200).

What kind of SQL query can I use to achieve this result? Is there some kind of SQL function I can use?

I would really appreciate some help.

I got some stuff online.

This was gotten from http://www.petefreitag.com/item/466.cfm

There are lots of ways to select a random record or row from a database table. Here are some example SQL statements that don't require additional application logic, but each database server requires different SQL syntax.

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2

SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

I am working with MySQL and the query for MySQL given at this site concurs with the ideas of kobojunkie and ade2kay.

Thanks y'all.  You've helped a lot.

No, that's wrong!

You can actually use numerals in the order by.
e.g. if you say select pdtName, pdtDesc, pdtPrice from pdtDetails order by 1, the select query orders by the first field in the select statement i.e. pdtName

or if you say select pdtName, pdtDesc, pdtPrice from pdtDetails order by 3, the select query orders by the 3rd field.

so order by rand() returns a random number which will now determine the field to order by.
I don't know what will happen however if the number returned by the rand() function is greater than the number of fields returned by the select query.

Find out !

I did some research and I found this on http://www.greggdev.com/web/articles.php?id=6

As of this writing, there is no automatic way for MySQL to select a random row from a database table. Extracting a random row from a table can be useful for many reasons. It can pull up random products, random advertisments, or any other random thing that you have stored in your database table.

For most purposes on smaller database tables, the following will work fine:

$random_row = mysql_fetch_row(mysql_query("select * from YOUR_TABLE order by rand() limit 1"));

$random_row will be an array containing the data extracted from the random row. However, when the table is large (over about 10,000 rows) this method of selecting a random row becomes increasingly slow with the size of the table and can create a great load on the server. I tested this on a table I was working that contained 2,394,968 rows. It took 717 seconds (12 minutes!) to return a random row.

I wrote the script below as a workaround for a random row with a large database table. This will return a random row in about 0.05 seconds, regardless of the size of the table. If the max_id of the table is not dynamically changing, the function can be rewritten to only execute one database query instead of two.

I am using this script for a new search engine spidering project I am working on. With this, it is useful to have an tinyint(1) column in the table that is updated as the row is selected so that it will not be chosen again. Since the function uses greater than and less than, it would not return an empty result unless all rows had been updated at which point the script could be ended. For such a script this also allows many updaters to be running at once selecting random rows that will not be selected again because the tinyint will be changed upon selection.


<?php
  //CODE FROM WWW.GREGGDEV.COM
  function random_row($table, $column) {
      $max_sql = "SELECT max(" . $column . ")
                  AS max_id
                  FROM " . $table;
      $max_row = mysql_fetch_array(mysql_query($max_sql));
      $random_number = mt_rand(1, $max_row['max_id']);
      $random_sql = "SELECT * FROM " . $table . "
                     WHERE " . $column . " >= " . $random_number . "
                     ORDER BY " . $column . " ASC
                     LIMIT 1";
      $random_row = mysql_fetch_row(mysql_query($random_sql));
      if (!is_array($random_row)) {
          $random_sql = "SELECT * FROM " . $table . "
                         WHERE " . $column . " < " . $random_number . "
                         ORDER BY " . $column . " DESC
                         LIMIT 1";
          $random_row = mysql_fetch_row(mysql_query($random_sql));
      }
      return $random_row;
  }
 
  //USAGE
  echo '<pre>';
  print_r(random_row('YOUR_TABLE', 'YOUR_COLUMN'));
  echo '</pre>';
?>


ThAs of this writing, there is no automatic way for MySQL to select a random row from a database table. Extracting a random row from a table can be useful for many reasons. It can pull up random products, random advertisments, or any other random thing that you have stored in your database table.

For most purposes on smaller database tables, the following will work fine:

$random_row = mysql_fetch_row(mysql_query("select * from YOUR_TABLE order by rand() limit 1"));

$random_row will be an array containing the data extracted from the random row. However, when the table is large (over about 10,000 rows) this method of selecting a random row becomes increasingly slow with the size of the table and can create a great load on the server. I tested this on a table I was working that contained 2,394,968 rows. It took 717 seconds (12 minutes!) to return a random row.

I wrote the script below as a workaround for a random row with a large database table. This will return a random row in about 0.05 seconds, regardless of the size of the table. If the max_id of the table is not dynamically changing, the function can be rewritten to only execute one database query instead of two.

I am using this script for a new search engine spidering project I am working on. With this, it is useful to have an tinyint(1) column in the table that is updated as the row is selected so that it will not be chosen again. Since the function uses greater than and less than, it would not return an empty result unless all rows had been updated at which point the script could be ended. For such a script this also allows many updaters to be running at once selecting random rows that will not be selected again because the tinyint will be changed upon selection.


<?php
  //CODE FROM WWW.GREGGDEV.COM
  function random_row($table, $column) {
      $max_sql = "SELECT max(" . $column . ")
                  AS max_id
                  FROM " . $table;
      $max_row = mysql_fetch_array(mysql_query($max_sql));
      $random_number = mt_rand(1, $max_row['max_id']);
      $random_sql = "SELECT * FROM " . $table . "
                     WHERE " . $column . " >= " . $random_number . "
                     ORDER BY " . $column . " ASC
                     LIMIT 1";
      $random_row = mysql_fetch_row(mysql_query($random_sql));
      if (!is_array($random_row)) {
          $random_sql = "SELECT * FROM " . $table . "
                         WHERE " . $column . " < " . $random_number . "
                         ORDER BY " . $column . " DESC
                         LIMIT 1";
          $random_row = mysql_fetch_row(mysql_query($random_sql));
      }
      return $random_row;
  }
 
  //USAGE
  echo '<pre>';
  print_r(random_row('YOUR_TABLE', 'YOUR_COLUMN'));
  echo '</pre>';
?>

As of this writing, there is no automatic way for MySQL to select a random row from a database table. Extracting a random row from a table can be useful for many reasons. It can pull up random products, random advertisments, or any other random thing that you have stored in your database table.

For most purposes on smaller database tables, the following will work fine:

$random_row = mysql_fetch_row(mysql_query("select * from YOUR_TABLE order by rand() limit 1"));

$random_row will be an array containing the data extracted from the random row. However, when the table is large (over about 10,000 rows) this method of selecting a random row becomes increasingly slow with the size of the table and can create a great load on the server. I tested this on a table I was working that contained 2,394,968 rows. It took 717 seconds (12 minutes!) to return a random row.

I wrote the script below as a workaround for a random row with a large database table. This will return a random row in about 0.05 seconds, regardless of the size of the table. If the max_id of the table is not dynamically changing, the function can be rewritten to only execute one database query instead of two.

I am using this script for a new search engine spidering project I am working on. With this, it is useful to have an tinyint(1) column in the table that is updated as the row is selected so that it will not be chosen again. Since the function uses greater than and less than, it would not return an empty result unless all rows had been updated at which point the script could be ended. For such a script this also allows many updaters to be running at once selecting random rows that will not be selected again because the tinyint will be changed upon selection.


<?php
  //CODE FROM WWW.GREGGDEV.COM
  function random_row($table, $column) {
      $max_sql = "SELECT max(" . $column . ")
                  AS max_id
                  FROM " . $table;
      $max_row = mysql_fetch_array(mysql_query($max_sql));
      $random_number = mt_rand(1, $max_row['max_id']);
      $random_sql = "SELECT * FROM " . $table . "
                     WHERE " . $column . " >= " . $random_number . "
                     ORDER BY " . $column . " ASC
                     LIMIT 1";
      $random_row = mysql_fetch_row(mysql_query($random_sql));
      if (!is_array($random_row)) {
          $random_sql = "SELECT * FROM " . $table . "
                         WHERE " . $column . " < " . $random_number . "
                         ORDER BY " . $column . " DESC
                         LIMIT 1";
          $random_row = mysql_fetch_row(mysql_query($random_sql));
      }
      return $random_row;
  }
 
  //USAGE
  echo '<pre>';
  print_r(random_row('YOUR_TABLE', 'YOUR_COLUMN'));
  echo '</pre>';
?>

Thanks to everyone for responding. The article above gives a solution from a PHP viewpoint (which is perfect since I am working with PHP) though the script above may be complex.

Interesting, i got here too late else i could hav joined by at least contributin some rubish code - ehm - i hav been tryin to do a "word of the day script" - i hav been tryin to figure out how to go about it - but hav not actually started coding it - i am suspecting using this random selection from table should solve the problem.

@Ghenghis.

even that trick wont work. It might look promising but it's best to put it to the test and see it fail.
real random numbers must come from outside your computer.

sound always work.