JIYIK CN >

This article aims to understand how to sort values ​​by date in MySQL.

Most of the businesses and organizations that use MySQL for data analysis or data visualization need to sort different table values ​​of their users based on date. Using SORT BYthe statement, we can do this efficiently in MySQL. Using this statement, we can arrange the values ​​of the table in any way we want.

For example, for a product-based company, if analysts want to sort the records of different users based on their registration date on the platform, they can use ORDER BYthe statement to do this.

Let us 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)
);

Use INSERTthe statement to insert entries into the MySQL table

The above query creates a student_datestable named . INSERTWith the help of add statement, let us add data for some students. The operation can be performed 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'));

This code will student_datesenter the student data in the table . We can visualize this table using the following command.

SELECT * from student_dates;

The code block will generate the following 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

SORT BYUse the statement to sort the values ​​of the table in MySQL

sort byAs mentioned above, we can sort the values ​​using the SELECT statement in MySQL . This logic can be extrapolated to dates as well. We can do this using the following syntax.

SELECT * from name_of_the_table
ORDER BY date_column;

As we can see above, all the records of the table will be sorted based on date. We can now apply this concept to our student_datestable.

We can do this using the following query.

SELECT * from student_dates
ORDER BY stu_date;

The output of the code is shown below.

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

As we can see in the code block, all student_datesthe table records are sorted based on the date. It can also be reversed, which means we can adjust the query so that the records are displayed in the order with the latest records displayed first.

This technique is equally useful and can be understood with the following syntax.

SELECT * from student_dates
ORDER BY stu_date DESC;

The output of the code block can be explained as follows.

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

Therefore, with the help of statement in MySQL SORT BY, we can effectively sort the records of a particular table based on date.