Database Reference
In-Depth Information
numeric rather than lexical sort.) The following two queries order the results based on
the second
id
segment. The first sorts lexically, the second numerically:
mysql>
SELECT * FROM housewares4
->
ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',2),'-',-1);
+---------------+------------------+
| id | description |
+---------------+------------------+
| 8-4-2-1 | microwave oven |
| 13-478-92-2 | dining table |
| 873-48-649-63 | garbage disposal |
| 27-48-534-2 | shower stall |
| 5764-56-89-72 | lavatory |
| 97-681-37-66 | bedside lamp |
+---------------+------------------+
mysql>
SELECT * FROM housewares4
->
ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',2),'-',-1)+0;
+---------------+------------------+
| id | description |
+---------------+------------------+
| 8-4-2-1 | microwave oven |
| 873-48-649-63 | garbage disposal |
| 27-48-534-2 | shower stall |
| 5764-56-89-72 | lavatory |
| 13-478-92-2 | dining table |
| 97-681-37-66 | bedside lamp |
+---------------+------------------+
The substring-extraction expressions here are messy, but at least the column values to
which we apply the expressions have a consistent number of segments. To sort values
that have varying numbers of segments, the job can be more difficult.
Recipe 7.9
shows
an example illustrating why that is.
7.9. Sorting Hostnames in Domain Order
Problem
You want to sort hostnames in domain order, with the rightmost parts of the names
more significant than the leftmost parts.
Solution
Break apart the names, and sort the pieces from right to left.
Discussion
Hostnames are strings and therefore their natural sort order is lexical. However, it's often
desirable to sort hostnames in domain order, where the rightmost segments of the
