Skip to content
Related Articles

Related Articles

Improve Article
SQL – SELECT RANDOM
  • Difficulty Level : Medium
  • Last Updated : 14 May, 2021

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 :

                   Customer Information
Customer IDCustomer NameE-Mail Address
1Srishtiabc@gmail.com
2Rajdeepdef@gmail.com
3Aman xxx@gmail.com
4Poojaxyz@gmail.com

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 wheher 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.

My Personal Notes arrow_drop_up
Recommended Articles
Page :