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.

You can start with this

Select * from TableName order By rand() limit 50

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.

Kobojunkie:

You can start with this

Select * from TableName order By rand() limit 50

Thanks too Kobojunkie, you opened up my thoughts to something else.

@davidt,
based on Kobo's query above, why not re-arrange the table with the "order by rand()" first, before selecting from it.

i.e. select * from (Select * from TableName order By rand()) limit 50

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

Select * from TableName order By rand() limit 50


I have a serious issue with these. Order By does not serve that purpose at all. If I understood his question correctly, he just wanted to select 50 random columns. Order by refers to columns and I even doubt id you can use numerals to order table selections. You can only use column names. How will the rand() generate column names. It can only generate numerals.

I suggest that he should look at the unique ids of the rows and if they are numerals then he should use the where clause and tie the rand() in generating the ids,

something like

Select * from TableName where id funtion rand() limit 50;


The function should be thought out in such a way that the table can generate up to 50 rows at a time

Godman_n:

I have a serious issue with these. Order By does not serve that purpose at all. If I understood his question correctly, he just wanted to select 50 random columns. Order by refers to columns and I even doubt id you can use numerals to order table selections. You can only use column names. How will the rand() generate column names. It can only generate numerals.

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 was about to point out the same thing Godman_n pointed out.

Finding out what happens when rand() generates a number greater than the number of fields would be interesting, I'm yet to try out this stuff but when I do, I'll keep everyone posted,

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.

Almost forgot about this. I see you were not able to work with the Statement I posted. I am not particularly a MySQL Developer but I know something like that would work on some other databases that I use. Glad you got your answer now.

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.

Lets remember something Random() is not random is psuedo random so what you have is an even distribution of numbers.
Making random number of result sets (without repeats)is difficult because of the context of randomness. A good trick would be to get the random numbers generated form the app and request a record or resultset with its index(like rownum in oracle) equal to the random number. It also means you can be sure the records would stay random by calling the same rand function or storing used indexes (so u wont use them again)

There are many algorithms that can be used to have the kind of effect you want, a sieve immediately comes to mind.

note:@davidt : who pays for random adverts ? what you want is adverts that are called an equal number of times in an unspecified order.

Getting randomness in nature is harder than you might think

nice discussion!

@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 ur computer.

sound always work.

Ghengis said just my mind. The best way to tackle some issues with programming might not necessarily be the fastest or the easiest. If your table has an identity column (auto or user-defined)- good. Get ur application generate 50 random numbers for you, U can even make it variable and flexible, such that without going into your DB u can dictate the number of rows u want from the front-end. Keep the random numbers in an array/collection, then send these numbers to ur table Use something like, WHERE [id] in array[1, 50]. By this u are sure of getting ur desired result at the same time haviing a very good control over ur prog. Explore ur Language and find out a good way of Randomizing. VB uses RANDOMIZE TIMER declaration with RND().