JIYIK CN >

This tutorial aims to understand 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()the SELECT function to help analysts access random records. In addition, we need to use the SELECT keyword to quickly retrieve data LIMITusing the SELECT statement.RAND()

For example, if analysts need to quickly get 10 records from a table of more than 100,000 records, they can use the function with LIMITthe keyword RAND(). Let's try to understand this statement 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)
);

Use INSERTthe statement student_datesto insert an entry 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 . We can visualize this table using 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

Use ORDER BYthe statement to randomly obtain student_datesthe value of the table

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

grammar:

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 apply this concept to our % student_datess table. We can do this using 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 above code block, all student_datesthe 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 of the block above, the values ​​are randomly ordered.

Now, to quickly get the details using a specific upper bound set to the random function, we can use LIMITthe keyword as suggested above. This can be done using the following syntax.

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

As shown in the query above, our goal is student_datesto fetch only three randomly fetched records from our table.

Output:

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

Therefore, with the help of ORDER BYthe statement and RAND()function and LIMITkeyword, we can effectively sort different records of a specific table in MySQL randomly and quickly.