Sometimes there is a need to fetch random record from the table. For example – You have a list of quotes stored in a table and you would like to display a random quote on GUI, in such case you would have to write an SQL query to fetch random record from a table of quotes. In this tutorial, we will see how to select a random record from a table using RAND function.
Real World examples:
1. Online exams, where you want to display a random question.
2. For an e-commerce website to display random featured items on home page.
3. Display a random featured image on a website
Selecting random rows from table in MySQL
Syntax:
Here N specifies the number of random rows, you want to fetch. For example: If you want to fetch only 1 random row then you can use the numeric 1 in place N.
SELECT column_name FROM table_name ORDER BY RAND() LIMIT N;
Example: When we forget the passwords, the system asks the random security questions to verify the identity. Lets take the same example. Here we have a table, where we have security questions stored and we are fetching a random question from the table.
The following SQL statement will fetch 1 random record from the table Questions
SELECT QUESTION FROM Questions ORDER BY RAND() LIMIT 1;
Table: Questions
QNO QUESTION ---- --------- 1 What is your pet’s name? 2 What is your favourite food? 3 In what year was your mother born?
Result:
What is your favourite food?
Note: The output may be different for you because the question is randomly selected. Also, when you run this command again, you may get a different output.
Select random row from table in PostgreSQL
To do the same thing in PostgreSQL, we use RANDOM() function instead of RAND().
SELECT column_name FROM table_name ORDER BY RANDOM() LIMIT 1;
Select random row from table in Microsoft SQL Server
SELECT TOP 1 column_name FROM table_name ORDER BY NEWID();
Select random row from table in Oracle
SELECT column_name FROM ( SELECT column_name FROM table_name ORDER BY dbms_random.value ) WHERE ROWNUM = 1;