How to Select Random Row in MySQL

In database operations, selecting random rows from a table is a common requirement for various applications, such as gaming, content recommendation, and statistical sampling. In this article, we learn different methods for selecting random rows in MySQL. We’ll understand various approaches, including the use of RAND(), ORDER BY with LIMIT through multiple examples, and so on.

Setting Up Environment

We will create an employee table and then we will insert at least 10 entries in the table. Let’s create a simple employee table having Emp_Id as primary key, apart from primary key we have other columns i.e. Name and Department. We used not null in the name and the department field.

The table that has been created is shown below:

Shows entries in the EMPLOYEE tablw

Ways to Select Random Row in MySQL

Method 1: Using Rand()

Rand( ) is used to generate random float value between 0 and 1 and the it is arranged in order by using “order by“. Limit can be changed from 1 to the no. of entries in the database. If we put limit one then it reflect only one row and if apply limit 2 then it will return 2 random rows and so on.

Example 1

Query:

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

Output:

uses of rand() with limit 2

Explanation: The above image displays only one row on the screen as it has been given the Limit of 1. The below image shows two rows on the screen as it has been given only two rows.

Example 2

Query:

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

Output:

uses of rand() with limit 2

Explanation: The below image has been given the limits of 5 so it reflects five rows on the console. Limited should be smaller than the no. of entries in the table.

Method 2: Based On The Condition

This method is used to select a row based on the condition that Emp_id should be greater than the multiplication of maximum Emp_Id and random value generated by RAND() function.

Example 1

Query:

SELECT * FROM EMPLOYEE  WHERE Emp_Id >= RAND() * (SELECT MAX(Emp_Id) FROM EMPLOYEE) ORDER BY Emp_Id LIMIT 1;

Output:

WHERE Emp_Id >= RAND() * (SELECT MAX(Emp_Id) FROM EMPLOYEE) limit 1

Explanation: “WHERE Emp_Id >= RAND() * (SELECT MAX(Emp_Id) FROM EMPLOYEE)” is used to select a row based on the condition that Emp_id should be greater than the multiplication of maximum Emp_Id and random value generated by RAND() function. The random function generate floating number between 0 and 1. “ORDER BY” is used to arrange the row in ascending order and limit is used to limited number of rows. The value of limit can from 1 to no. of entries in the database.

Example 2

Query:

SELECT * FROM EMPLOYEE  WHERE Emp_Id >= RAND() * (SELECT MAX(Emp_Id) FROM EMPLOYEE) ORDER BY Emp_Id LIMIT 5;

Output:

WHERE Emp_Id >= RAND() * (SELECT MAX(Emp_Id) FROM EMPLOYEE) limit 5

Explanation: The above queries will display two random rowsas it has been given the limit of 5.

Method 3: Using SubQuery

It is a subquery based query that is used to generate a random float value using rand() function, then multiplied by the max ‘Emp_Id’ in the table and takes the ceil value of the multiplied result.

Example 1

Query:

SELECT e.*
FROM EMPLOYEE e
JOIN (
    SELECT CEIL(RAND() * (SELECT MAX(Emp_Id) FROM EMPLOYEE)) AS rand_id
) r ON e.Emp_Id >= r.rand_id
ORDER BY e.Emp_Id
LIMIT 1;

Output:

Mthod 3 using limit 1

Explanation: The above image display only one row as it has been the limit of 1.

Example 2

Query:

SELECT e.*
FROM EMPLOYEE e
JOIN (
    SELECT CEIL(RAND() * (SELECT MAX(Emp_Id) FROM EMPLOYEE)) AS rand_id
) r ON e.Emp_Id >= r.rand_id
ORDER BY e.Emp_Id
LIMIT 2;

Output:

Mthod 3 using limit 4

Explanation: The above image shows the two row random rows of the employee table.

Example 3:

Query:

SELECT e.*
FROM EMPLOYEE e
JOIN (
    SELECT CEIL(RAND() * (SELECT MAX(Emp_Id) FROM EMPLOYEE)) AS rand_id
) r ON e.Emp_Id >= r.rand_id
ORDER BY e.Emp_Id
LIMIT 4;

Output:

Mthod 3 using limit 4

Explanation: In the above query we have fetch a random set of records from the EMPLOYEE table by generating a random Emp_Id within the range of existing IDs. It joins the EMPLOYEE table with a subquery that generates a random ID, filters records based on this random ID, orders them by Emp_Id, and limits the output to four random records.

Conclusion

There are many ways by which we can select the random row in MySQL. We can select the row by using a random function we can also use a subquery inside the main query to display the number of rows. Above we have discussed the three methods by which we can select random rows. Limit plays the role of applying restriction on the number of rows. Its value can be variable from 1 to the total number of rows in the database. It displays the number of rows below the limit only. If we use the limit value of 0, it will not display anything on the command shell. It is always recommended to use the value of limit from 1 to no. of rows in the database.