How to Sort by Date in MySQL

1. Use the INSERT Statement to Insert Entries in a Table in MySQL
2. Use the SORT BY Statement to Sort the Values of a Table in MySQL

This tutorial aims to understand how to sort values by dates in MySQL.

Most 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 the SORT BY statement, we can efficiently do this in MySQL. Using this statement, we can arrange the values of the table in any way we wish to arrange.

For example, for a product-based company, if the analyst wishes to sort the records of different users based on their date of registration on the platform, they can use the ORDER BY statement to get this done.

Let us 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, and 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 the INSERT Statement to Insert Entries in a Table in MySQL

The above query creates a table with the name student_dates. With the INSERT statement’s help, let us 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 would enter the student data in the table student_dates. We can visualize this table with the following command.

SELECT * from student_dates;

The code block would 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

Use the SORT BY Statement to Sort the Values of a Table in MySQL

As mentioned above, we can use the sort by statement in MySQL to sort values. This logic can also be extrapolated to dates. We can do this with the following syntax.

SELECT * from name_of_the_table
ORDER BY date_column;

As we can see above, all the table’s records will order based on the date. We can now apply this concept to our student_dates table.

We can perform this operation with the following query.

SELECT * from student_dates
ORDER BY stu_date;

The output of the code can be illustrated as follows.

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

All the student_dates table records are sorted based on the dates, as we see in the code block. It can also be reversed, meaning we can adjust our query such that the records are displayed in the order where the newest records are shown 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 illustrated 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

Thus with the help of the SORT BY statement in MySQL, we can efficiently sort records of a particular table based on dates.