SQL – SELECT RANDOM

RANDOM( ) in SQL is generally used to return a random row from a table present in the database. It has many applications in real life. 

For example : 

1. There are a lot of employees in an organization. Suppose, if the event manager wants to mail any ten random employees then he/she can use the RANDOM( ) in SQL to get the Email Id of the ten random employees.
2. It can also be used to display random questions during an online exam or MCQ from a pool of questions.

In this article, we are going to discuss how RANDOM( ) can be used using a sample table shown below.

Sample Input Table :

BASIC SQL QUERY :

1. Creating a Database

CREATE DATABASE database_name;

2. Creating a Table

CREATE TABLE Table_name(
col_1 TYPE col_1_constraint,
col_2 TYPE col_2 constraint
.....
)

col: Column name
TYPE: Data type whether an integer, variable character, etc
col_constraint: Constraints in SQL like PRIMARY KEY, NOT NULL, UNIQUE, REFERENCES, etc

3. Inserting into a Table

INSERT INTO Table_name
VALUES(val_1, val_2, val_3, ..........)

val: Values in particular column

4. View The Table

SELECT * FROM Table_name

Output :

Customer Table

SQL QUERY FOR RANDOM :

1. MYSQL

SELECT col_1,col_2, ... FROM Table_Name
ORDER BY RAND()

col_1 : Column 1
col_2 : Column 2

The above query will return the entire table for the specific columns mentioned and the rows will be random and changing position every time we run the query. To get a single row randomly, we can use the LIMIT Clause and set to only one row. ORDER BY clause in the query is used to order the row(s) randomly.

SELECT col_1,col_2, ... FROM Table_Name
ORDER BY RAND()
LIMIT 1

col_1 : Column 1
col_2 : Column 2

2. PostgreSQL and SQLite

It is exactly the same as MYSQL. Just replace RAND( ) with RANDOM( ).

SELECT col_1,col_2, ... FROM Table_Name
ORDER BY RAND()
LIMIT 1

col_1 : Column 1
col_2 : Column 2

Output :

Random Rows

RANDOM ROWS

RANDOM ROWS

RANDOM ROW

RANDOM ROW

We can observe that the above queries return the rows randomly from all the set of rows in the table. The RANDOM( ) clause is beneficial when there are humongous records in the database.

rishabhchakrabortygfg

Follow

Improve