Sử dụng ORDER BY khi tính toán ngày tháng trong MySQL

Dùng ORDER BY khi tính ngày để dễ tìm kiếm bản ghi. Dữ liệu từ Collegedetail:

mysql> SELECT * FROM Collegedetail;
+------+---------+------------+
| ID   | Country | Estb       |
+------+---------+------------+
| 111  | INDIA   | 2010-05-01 |
| 130  | INDIA   | 1995-10-25 |
| 139  | USA     | 1994-09-25 |
| 1539 | UK      | 2001-07-23 |
| 1545 | Russia  | 2010-07-30 |
+------+---------+------------+
5 rows in set (0.00 sec)

Tính tuổi trường (số năm):

mysql> SELECT ID, estb, CURDATE(),
    ((YEAR(CURDATE())-YEAR(estb))-(RIGHT(CURDATE(),5)<RIGHT(estb,5))) AS 'YEARS_OLD'
    FROM collegedetail;
+------+------------+------------+-----------+
| ID   | estb       | CURDATE()  | YEARS_OLD |
+------+------------+------------+-----------+
| 111  | 2010-05-01 | 2017-11-30 | 7         |
| 130  | 1995-10-25 | 2017-11-30 | 22        |
| 139  | 1994-09-25 | 2017-11-30 | 23        |
| 1539 | 2001-07-23 | 2017-11-30 | 16        |
| 1545 | 2010-07-30 | 2017-11-30 | 7         |
+------+------------+------------+-----------+
5 rows in set (0.00 sec)

Thêm ORDER BY YEARS_OLD (tăng dần):

mysql> SELECT ID, estb, CURDATE(),
    ((YEAR(CURDATE())-YEAR(estb))-(RIGHT(CURDATE(),5)<RIGHT(estb,5))) AS 'YEARS_OLD'
    FROM collegedetail ORDER BY YEARS_OLD;
+------+------------+------------+-----------+
| ID   | estb       | CURDATE()  | YEARS_OLD |
+------+------------+------------+-----------+
| 111  | 2010-05-01 | 2017-11-30 | 7         |
| 1545 | 2010-07-30 | 2017-11-30 | 7         |
| 1539 | 2001-07-23 | 2017-11-30 | 16        |
| 130  | 1995-10-25 | 2017-11-30 | 22        |
| 139  | 1994-09-25 | 2017-11-30 | 23        |
+------+------------+------------+-----------+
5 rows in set (0.01 sec)

Dùng ORDER BY YEARS_OLD DESC (giảm dần - trường cũ nhất lên đầu):

mysql> SELECT ID, estb, CURDATE(),
    ((YEAR(CURDATE())-YEAR(estb))-(RIGHT(CURDATE(),5)<RIGHT(estb,5))) AS 'YEARS_OLD'
    FROM collegedetail ORDER BY YEARS_OLD DESC;
+------+------------+------------+-----------+
| ID   | estb       | CURDATE()  | YEARS_OLD |
+------+------------+------------+-----------+
| 139  | 1994-09-25 | 2017-11-30 | 23        |
| 130  | 1995-10-25 | 2017-11-30 | 22        |
| 1539 | 2001-07-23 | 2017-11-30 | 16        |
| 111  | 2010-05-01 | 2017-11-30 | 7         |
| 1545 | 2010-07-30 | 2017-11-30 | 7         |
+------+------------+------------+-----------+
5 rows in set (0.00 sec)