How to Fetch Random Values in MySQL

1. Inserting Entries in the student_dates Table Using the INSERT Statement
2. Randomly Fetching the Values of student_dates Table Using the ORDER BY Statement

This tutorial aims to understand how to sort or order values or records of a table randomly in MySQL.

Most businesses and organizations that use MySQL for data analysis or visualization need to sort different table values of their users based on different criteria.

One of the most efficient techniques to test whether different users in a MySQL table are checked correctly is getting access to users randomly. It can help avoid conflicts and better understand the user based on a particular platform.

MySQL assists analysts in getting access to random records using the RAND() function. Moreover, we need to use the LIMIT keyword to fetch data quickly using the RAND() statement.

For example, if analysts need to quickly fetch ten records from a table with more than 100,000 records, they can use the RAND() function with the LIMIT keyword. Let’s try to understand this statement in greater depth.

However, before we begin, we create a dummy dataset to work with. Here we create a table, student_dates, along with a few rows.

-- create the table student_dates
CREATE TABLE student_dates(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_date date,
  primary key(stu_id)
);

Inserting Entries in the student_dates Table Using the INSERT Statement

The previous query creates a table with the name student_dates. Now with the help of the INSERT statement, let us try to add data for a few students. This operation can be done as follows:

-- insert rows to the table student_dates
INSERT INTO student_dates(stu_id,stu_firstName,stu_date) 
 VALUES(1,"Preet",STR_TO_DATE('24-May-2005', '%d-%M-%Y')),
 (2,"Dhruv",STR_TO_DATE('14-June-2001', '%d-%M-%Y')),
 (3,"Mathew",STR_TO_DATE('13-December-2020', '%d-%M-%Y')),
 (4,"Jeet",STR_TO_DATE('14-May-2003', '%d-%M-%Y')),
 (5,"Steyn",STR_TO_DATE('19-July-2002', '%d-%M-%Y'));

The code above would enter the student data in the table student_dates. We can visualize this table with the following command.

SELECT * from student_dates;

Output:

stu_id	stu_firstName	stu_date
1		Preet			2005-05-24
2		Dhruv			2001-06-14
3		Mathew			2020-12-13
4		Jeet			2003-05-14
5		Steyn			2002-07-19

Randomly Fetching the Values of student_dates Table Using the ORDER BY Statement

As mentioned above, we can use the sort by statement in MySQL to sort values. This logic can also be used to sort records in a table randomly.

Syntax:

SELECT * from name_of_the_table
ORDER BY RAND();

As we can see above, all the table records would be ordered randomly using the RAND() function. We can apply this concept to our student_dates table. This operation can be performed with the following query.

SELECT * from student_dates
ORDER BY RAND();

Output:

stu_id	stu_firstName	stu_date
5		Steyn			2002-07-19
3		Mathew			2020-12-13
4		Jeet			2003-05-14
1		Preet			2005-05-24
2		Dhruv			2001-06-14

As we can see in the aforementioned code block, all the student_dates table records are sorted randomly.

Output:

stu_id	stu_firstName	stu_date
4		Jeet			2003-05-14
1		Preet			2005-05-24
2		Dhruv			2001-06-14
3		Mathew			2020-12-13
5		Steyn			2002-07-19

As we can see from the output above blocks, the values are sorted randomly.

Now, to fetch details quickly with a particular upper limit set to the random function, we can use the LIMIT keyword as suggested above. This operation can be performed with the following syntax.

SELECT * from student_dates
ORDER BY RAND()
LIMIT 3;

As shown in the query above, we aim to fetch only three randomly fetched records from our student_dates table.

Output:

stu_id	stu_firstName	stu_date
2		Dhruv			2001-06-14
1		Preet			2005-05-24
3		Mathew			2020-12-13

Thus with the help of the ORDER BY statement and the RAND() function, and the LIMIT keyword, we can efficiently order different records of a particular table in MySQL randomly and quickly.