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
hostname values are more significant than the leftmost segments.
Suppose you have a table hostname that contains
the following names:
mysql> SELECT name FROM hostname ORDER BY name;
+--------------------+
| name |
+--------------------+
| cvs.php.net |
| dbi.perl.org |
| jakarta.apache.org |
| lists.mysql.com |
| mysql.com |
| www.kitebird.com |
+--------------------+The preceding query demonstrates the natural lexical sort order of
the name values. That differs from domain order,
as shown by the following table:
|
Lexical order |
Domain order |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Producing domain-ordered output is a substring-sorting problem, where
it’s necessary to extract each segment of the names
so they can be sorted in right-to-left fashion. There is also an
additional complication if your values contain different numbers of
segments, as our example hostnames do. (Most of them have three
segments, but mysql.com has only ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
