How to Sort by Date in MySQL
-
Use the
INSERT
Statement to Insert Entries in a Table in MySQL -
Use the
SORT BY
Statement to Sort the Values of a Table in MySQL
![How to Sort by Date in MySQL](/img/MySQL/feature-image---mysql-sort-by-date.webp)
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.