Sorting Query Results - MySQL - page 249

importantly, you can index the date-part columns separately—something not possible
with a DATE column. The principle here is that you should design the table to make it
easy to extract or sort by the values that you expect to use a lot.
Sorting by day of week
Day-of-week sorting is similar to calendar-day sorting, except that you use different
functions to obtain the relevant ordering values.
You can get the day of the week using DAYNAME() , but that produces strings that sort
lexically rather than in day-of-week order (Sunday, Monday, Tuesday, and so forth).
Here the technique of displaying one value but sorting by another is useful (see
Recipe 7.3 ). Display day names using DAYNAME() , but sort in day-of-week order using
DAYOFWEEK() , which returns numeric values from 1 to 7 for Sunday through Saturday:
mysql> SELECT DAYNAME(date) AS day, date, description
-> FROM occasion
-> ORDER BY DAYOFWEEK(date);
+----------+------------+-------------------------------------+
| day | date | description |
+----------+------------+-------------------------------------+
| Sunday | 1776-07-14 | Bastille Day |
| Sunday | 1809-02-12 | Abraham Lincoln's birthday |
| Monday | 1215-06-15 | Signing of the Magna Carta |
| Tuesday | 1944-06-06 | D-Day at Normandy Beaches |
| Thursday | 1989-11-09 | Opening of the Berlin Wall |
| Friday | 1957-10-04 | Sputnik launch date |
| Friday | 1732-02-22 | George Washington's birthday |
| Saturday | 1789-07-04 | US Independence Day |
| Saturday | 1919-06-28 | Signing of the Treaty of Versailles |
+----------+------------+-------------------------------------+
To sort rows in day-of-week order but treat Monday as the first day of the week and
Sunday as the last, use the MOD() function to map Monday to 0, Tuesday to 1, …, Sunday
to 6:
mysql> SELECT DAYNAME(date), date, description
-> FROM occasion
-> ORDER BY MOD(DAYOFWEEK(date)+5, 7);
+---------------+------------+-------------------------------------+
| DAYNAME(date) | date | description |
+---------------+------------+-------------------------------------+
| Monday | 1215-06-15 | Signing of the Magna Carta |
| Tuesday | 1944-06-06 | D-Day at Normandy Beaches |
| Thursday | 1989-11-09 | Opening of the Berlin Wall |
| Friday | 1957-10-04 | Sputnik launch date |
| Friday | 1732-02-22 | George Washington's birthday |
| Saturday | 1789-07-04 | US Independence Day |
| Saturday | 1919-06-28 | Signing of the Treaty of Versailles |
| Sunday | 1776-07-14 | Bastille Day |