MySQL - Select Random Records

Have you ever taken online examinations? If yes, then did you ever wonder how is the order, in which these questions are displayed, random? These questions are usually stored in a database of the test application and are randomly displayed one by one.

While using a database for an application, there arise situations where the records from a table object need to be selected randomly. MySQL does not have a built-in provision for this.

Selecting Random Records in MySQL

In order to select random records in MySQL, you can use the ORDER BY RAND() clause. The RAND() function is used with the SELECT query to retrieve the stored data one by one or collectively together.

The MySQL RAND() Function

The MySQL RAND() Function returns a result-set containing all records of the original table in a completely random order. It is usually used with a SELECT statement in the ORDER BY clause.

Syntax

Following is the basic syntax of the RAND() function with ORDER BY Clause −

SELECT column_name(s) FROM table_name ORDER BY RAND();

Example

Following example demonstrates the usage of RAND() function when used with ORDER BY Clause. Here, let us first create a table 'CUSTOMERS' and insert some values into it.

CREATE TABLE CUSTOMERS(
   ID int NOT NULL AUTO_INCREMENT,
   NAME varchar(20),
   AGE int,
   PRIMARY KEY(Id)
);

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS (NAME, AGE) VALUES ('John',23);
INSERT INTO CUSTOMERS (NAME, AGE) VALUES ('Larry',21);
INSERT INTO CUSTOMERS (NAME, AGE) VALUES ('David',21);
INSERT INTO CUSTOMERS (NAME, AGE) VALUES ('Carol',24);
INSERT INTO CUSTOMERS (NAME, AGE) VALUES ('Bob',27);
INSERT INTO CUSTOMERS (NAME, AGE) VALUES ('Mike',29);
INSERT INTO CUSTOMERS (NAME, AGE) VALUES ('Sam',26);

The CUSTOMERS table obtained is as follows −

Now, let us use the RAND() function with the SELECT statement to retrieve the records of the CUSTOMERS table in a randomized order −

SELECT * FROM CUSTOMERS ORDER BY RAND();

Output

Following is the output of the above query −

LIMIT with RAND() Function

You can also limit the number of randomly retrieved records using the LIMIT clause with the RAND() function

Syntax

Following is the syntax to use LIMIT with RAND() function −

SELECT column_name(s) FROM table_name 
ORDER BY RAND() LIMIT int_value;

Example

In this example, we are retrieving a limited number of records at random from the 'CUSTOMERS' table using the following query −

SELECT * FROM CUSTOMERS ORDER BY RAND() LIMIT 1;

Output of the above code is as shown below −

Each time you execute this query, you will get a different random record −

SELECT * FROM CUSTOMERS ORDER BY RAND() LIMIT 1;

The result produced is as follows −

You can also increase the limit of records to be displayed by modifying the LIMIT value as shown below −

SELECT * FROM CUSTOMERS ORDER BY RAND() LIMIT 2;

We get the output as shown below −

Random Records Using Client Program

We can also select random records using client program.

Syntax

$sql = "SELECT * FROM CUSTOMERS ORDER BY RAND()";
$mysqli->query($sql);

sql = "SELECT * FROM CUSTOMERS ORDER BY RAND()";
con.query(sql)

String sql = "SELECT * FROM CUSTOMERS ORDER BY RAND()";
statement.executeQuery(sql);

random_query = "SELECT * FROM CUSTOMERS ORDER BY RAND()"
cursorObj.execute(random_query)

Example

Following are the programs −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.
');
//let's create a table
$sql = "create table CUSTOMERS ( Id int NOT NULL AUTO_INCREMENT, Name varchar(20), Age int, PRIMARY KEY(Id) )";
if($mysqli->query($sql)){
    printf("CUSTOMERS table created successfully...!\n");
}
//now lets insert some records
$sql = "insert into CUSTOMERS(Name,Age) values('John',23)";
if($mysqli->query($sql)){
    printf("First record inserted successfully....!\n");
}
$sql = "insert into CUSTOMERS(Name,Age) values('Larry',21)";
if($mysqli->query($sql)){
    printf("Second record inserted successfully....!\n");
}
$sql = "insert into CUSTOMERS(Name,Age) values('David',21)";
if($mysqli->query($sql)){
    printf("Third record inserted successfully....!\n");
}
$sql = "insert into CUSTOMERS(Name,Age) values('Carol',24)";
if($mysqli->query($sql)){
    printf("Fourth record inserted successfully....!\n");
}
$sql = "insert into CUSTOMERS(Name,Age) values('Bob',27)";
if($mysqli->query($sql)){
    printf("Fifth record inserted successfully....!\n");
}
//display table record
$sql = "SELECT * FROM CUSTOMERS";
if($result = $mysqli->query($sql)){
    printf("Table records: \n");
    while($row = mysqli_fetch_array($result)){
        printf("Id: %d, Name: %s, Age: %d", 
            $row['Id'], 
            $row['Name'], 
            $row['Age']);
            printf("\n");
    }
}
//lets find random record
$sql = "SELECT * FROM CUSTOMERS ORDER BY RAND()";
if($result = $mysqli->query($sql)){
    printf("Table records(random record): \n");
    while($row = mysqli_fetch_array($result)){
        printf("Id: %d, Name: %s, Age: %d", 
            $row['Id'], 
            $row['Name'], 
            $row['Age']);
            printf("\n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

CUSTOMERS table created successfully...!
First record inserted successfully....!
Second record inserted successfully....!
Third record inserted successfully....!
Fourth record inserted successfully....!
Fifth record inserted successfully....!
Table records:
Id: 1, Name: John, Age: 23
Id: 2, Name: Larry, Age: 21
Id: 3, Name: David, Age: 21
Id: 4, Name: Carol, Age: 24
Id: 5, Name: Bob, Age: 27
Table records(random record):
Id: 3, Name: David, Age: 21
Id: 1, Name: John, Age: 23
Id: 2, Name: Larry, Age: 21
Id: 4, Name: Carol, Age: 24
Id: 5, Name: Bob, Age: 27    

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

// Connecting to MySQL
con.connect(function (err) {
    if (err) throw err;
    console.log("Connected!");
    console.log("--------------------------");

    // Create a new database
    sql = "Create Database TUTORIALS";
    con.query(sql);

    sql = "USE TUTORIALS";
    con.query(sql);

    //Creating CUSTOMERS table
    sql = "create table CUSTOMERS(Id int NOT NULL AUTO_INCREMENT,Name varchar(20),Age int,PRIMARY KEY(Id));"
    con.query(sql);

    sql = "insert into CUSTOMERS(Name,Age) values('John',23),('Larry',21),('David',21),('Carol',24),('Bob',27),('Mike',29),('Sam',26);"
    con.query(sql);

    sql = "SELECT * FROM CUSTOMERS;"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log("**Records in CUSTOMERS Table**");
      console.log(result);
      console.log("--------------------------");
    });

    sql = "SELECT * FROM CUSTOMERS ORDER BY RAND();"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log("**Retrieving the randomized order of records:**");
      console.log(result);
    });
});

 
Connected!
--------------------------
**Records in CUSTOMERS Table**
[
  { Id: 1, Name: 'John', Age: 23 },
  { Id: 2, Name: 'Larry', Age: 21 },
  { Id: 3, Name: 'David', Age: 21 },
  { Id: 4, Name: 'Carol', Age: 24 },
  { Id: 5, Name: 'Bob', Age: 27 },
  { Id: 6, Name: 'Mike', Age: 29 },
  { Id: 7, Name: 'Sam', Age: 26 }
]
--------------------------
**Retrieving the randomized order of records:**
[
  { Id: 5, Name: 'Bob', Age: 27 },
  { Id: 4, Name: 'Carol', Age: 24 },
  { Id: 6, Name: 'Mike', Age: 29 },
  { Id: 2, Name: 'Larry', Age: 21 },
  { Id: 1, Name: 'John', Age: 23 },
  { Id: 7, Name: 'Sam', Age: 26 },
  { Id: 3, Name: 'David', Age: 21 }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class RandomRecords {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "create table CUSTOMERS(Id int NOT NULL AUTO_INCREMENT, Name varchar(20), Age int, PRIMARY KEY(Id))";
            st.execute(sql);
            System.out.println("Table CUSTOMERS created successfully...!");
            //let's insert some records into it...
            String sql1 = "INSERT INTO CUSTOMERS VALUES (NULL, 'John',23), (NULL, 'Larry',21), (NULL, 'David',21), (NULL, 'Carol',24), (NULL, 'Bob',27)";
            st.execute(sql1);
            System.out.println("Records inserted successfully....!");
            //print table records
            String sql2 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql2);
            System.out.println("Table records: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age);
            }
            //lets print random records
            String sql4 = "SELECT * FROM CUSTOMERS ORDER BY RAND()";
            rs = st.executeQuery(sql4);
            System.out.println("Table records(random records): ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}    

Table CUSTOMERS created successfully...!
Records inserted successfully....!
Table records: 
Id: 1, Name: John, Age: 23
Id: 2, Name: Larry, Age: 21
Id: 3, Name: David, Age: 21
Id: 4, Name: Carol, Age: 24
Id: 5, Name: Bob, Age: 27
Table records(random records): 
Id: 5, Name: Bob, Age: 27
Id: 1, Name: John, Age: 23
Id: 2, Name: Larry, Age: 21
Id: 4, Name: Carol, Age: 24
Id: 3, Name: David, Age: 21    

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object
cursorObj = connection.cursor()
# Creating the table 'CUSTOMERS'
create_table_query = '''
CREATE TABLE CUSTOMERS (
    Id INT NOT NULL AUTO_INCREMENT,
    Name VARCHAR(20),
    Age INT,
    PRIMARY KEY(Id)
);
'''
cursorObj.execute(create_table_query)
print("Table 'CUSTOMERS' is created successfully!")
# Inserting records into 'CUSTOMERS' table
sql = "INSERT INTO CUSTOMERS (Name, Age) VALUES (%s, %s);"
values = [('John', 23), ('Larry', 21), ('David', 21), ('Carol', 24), ('Bob', 27), ('Mike', 29), ('Sam', 26)]
cursorObj.executemany(sql, values)
print("Values inserted successfully!")
# Display table
display_table_query = "SELECT * FROM CUSTOMERS;"
cursorObj.execute(display_table_query)
# Printing the table 'CUSTOMERS'
results = cursorObj.fetchall()
print("\nCUSTOMERS Table:")
for result in results:
    print(result)
# Retrieve the randomized order of records in the 'CUSTOMERS' table
random_query = "SELECT * FROM CUSTOMERS ORDER BY RAND()";
cursorObj.execute(random_query)
results = cursorObj.fetchall()
print("\nRandomized CUSTOMERS Table:")
for result in results:
    print(result)
# Closing the cursor and connection
cursorObj.close()
connection.close()   

Table 'CUSTOMERS' is created successfully!
Values inserted successfully!

CUSTOMERS Table:
(1, 'John', 23)
(2, 'Larry', 21)
(3, 'David', 21)
(4, 'Carol', 24)
(5, 'Bob', 27)
(6, 'Mike', 29)
(7, 'Sam', 26)

Randomized CUSTOMERS Table:
(7, 'Sam', 26)
(6, 'Mike', 29)
(3, 'David', 21)
(1, 'John', 23)
(2, 'Larry', 21)
(5, 'Bob', 27)
(4, 'Carol', 24)