Sorting Query Results - MySQL - page 248

mysql> SELECT date, description FROM occasion ORDER BY date;
+------------+-------------------------------------+
| date | description |
+------------+-------------------------------------+
| 1215-06-15 | Signing of the Magna Carta |
| 1732-02-22 | George Washington's birthday |
| 1776-07-14 | Bastille Day |
| 1789-07-04 | US Independence Day |
| 1809-02-12 | Abraham Lincoln's birthday |
| 1919-06-28 | Signing of the Treaty of Versailles |
| 1944-06-06 | D-Day at Normandy Beaches |
| 1957-10-04 | Sputnik launch date |
| 1989-11-09 | Opening of the Berlin Wall |
+------------+-------------------------------------+
To put these items in calendar order, sort them by month and day within month:
mysql> SELECT date, description FROM occasion
-> ORDER BY MONTH(date), DAYOFMONTH(date);
+------------+-------------------------------------+
| date | description |
+------------+-------------------------------------+
| 1809-02-12 | Abraham Lincoln's birthday |
| 1732-02-22 | George Washington's birthday |
| 1944-06-06 | D-Day at Normandy Beaches |
| 1215-06-15 | Signing of the Magna Carta |
| 1919-06-28 | Signing of the Treaty of Versailles |
| 1789-07-04 | US Independence Day |
| 1776-07-14 | Bastille Day |
| 1957-10-04 | Sputnik launch date |
| 1989-11-09 | Opening of the Berlin Wall |
+------------+-------------------------------------+
MySQL has a DAYOFYEAR() function that you might suspect would be useful for
calendar-day sorting. However, it can generate the same value for different calendar
days. For example, February 29 of leap years and March 1 of nonleap years have the
same day-of-year value:
mysql> SELECT DAYOFYEAR('1996-02-29'), DAYOFYEAR('1997-03-01');
+-------------------------+-------------------------+
| DAYOFYEAR('1996-02-29') | DAYOFYEAR('1997-03-01') |
+-------------------------+-------------------------+
| 60 | 60 |
+-------------------------+-------------------------+
This means that DAYOFYEAR() can group dates that actually occur on different calendar
days.
If a table represents dates using separate year, month, and day columns, calendar sorting
requires no date-part extraction. Just sort the relevant columns directly. For large da‐
tasets, sorting using separate date-part columns can be much faster than sorts based on
extracting pieces of DATE values. There's no overhead for part extraction, but more