Database Reference
In-Depth Information
| Sunday | 1809-02-12 | Abraham Lincoln's birthday |
+---------------+------------+-------------------------------------+
The following table shows the
DAYOFWEEK()
expressions for putting any day of the week
first in the sort order:
Day to list first
DAYOFWEEK() expression
Sunday
DAYOFWEEK(date)
Monday
MOD(DAYOFWEEK(date)+5, 7)
Tuesday
MOD(DAYOFWEEK(date)+4, 7)
Wednesday
MOD(DAYOFWEEK(date)+3, 7)
Thursday
MOD(DAYOFWEEK(date)+2, 7)
Friday
MOD(DAYOFWEEK(date)+1, 7)
Saturday
MOD(DAYOFWEEK(date)+0, 7)
You can also use
WEEKDAY()
for day-of-week sorting, although it returns a different set
of values (0 for Monday through 6 for Sunday).
7.6. Sorting by Substrings of Column Values
Problem
You want to sort a set of values using one or more substrings of each value.
Solution
Extract the pieces you want and sort them separately.
Discussion
This is a specific application of sorting by expression value (see
Recipe 7.2
). To sort rows
using just a particular portion of a column's values, extract the substring you need and
use it in the
ORDER
BY
clause. This is easiest if the substrings are at a fixed position and
length within the column. For substrings of variable position or length, you can still use
them for sorting if you have a reliable way to identify them. The next several recipes
show how to use substring extraction to produce specialized sort orders.
7.7. Sorting by Fixed-Length Substrings
Problem
You want to sort using parts of a column that occur at a given position within the column.
