JIYIK CN >

Summary: in this tutorial, we will learn how to shuffle or sort the values ​​or records of a table in MySQL.

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

One of the most effective techniques to test whether different users in MySQL tables are correctly checked is to access users randomly. It can help avoid conflicts and better understand users based on specific platforms.

MySQL uses RAND()functions to help analysts access random records. Let's try to understand this sentence more deeply.

However, before we begin, let's create a dummy dataset to work with. Here, we create a table student_datesand 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)
);

In MySQL, use INSERTthe statement student_datesto insert entries into the table

The previous query created a student_datestable called . Now INSERTwith the help of the statement, let's try to add data for some students. This 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 above code will student_datesenter the student data in the table.

ORDER BYUse the statement in MySQL student_datesto randomly sort the values ​​of the table

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

SELECT * from name_of_the_table
ORDER BY RAND();

As we can see above, all the table records will be RAND()randomly sorted using the %s function. We can now apply this concept to our % student_datess table. This can be done using the following query.

SELECT * from student_dates
ORDER BY RAND();

The output of the above code can be explained 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 above code block, all student_datesthe table records are sorted randomly. If the above query is executed a few more times, the output will be as shown below.

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 above output block, the values ​​are randomly ordered.

Therefore, with the help of ORDER BYstatement and RAND()function, we can effectively randomize the different records of a particular table in MySQL.