SQL Select Random Rows from Table

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;