How to ORDER BY RAND in MySQL

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

In this tutorial, we will 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. 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 in MySQL

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.

Randomly Sorting the Values of student_dates Table Using the ORDER BY Statement in MySQL

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

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 now apply this concept to our student_dates table. This operation can be performed with the following query.

SELECT * from student_dates
ORDER BY RAND();

The output of the aforementioned code can be illustrated as follows.

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. If the query above is executed a couple more times, the output will look like this.

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

The values are sorted randomly, as we can see from the aforementioned output blocks.

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