Sorting Query Results - MySQL - page 259

hostname values are more significant than the leftmost segments. Suppose that a host
name table contains the following names:

mysql> SELECT name FROM hostname ORDER BY name;
+--------------------+
| name |
+--------------------+
| dbi.perl.org |
| jakarta.apache.org |
| lists.mysql.com |
| mysql.com |
| svn.php.net |
| www.kitebird.com |
+--------------------+
The preceding query demonstrates the natural lexical sort order of the name values. That
differs from domain order, as the following table shows:

Lexical order Domain order
dbi.perl.org www.kitebird.com
jakarta.apache.org mysql.com
lists.mysql.com lists.mysql.com

mysql.com svn.php.net

svn.php.net jakarta.apache.org

www.kitebird.com dbi.perl.org

Producing domain-ordered output is a substring-sorting problem for which it's neces‐
sary 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 two.)
To extract the pieces of the hostnames, begin by using SUBSTRING_INDEX() in a manner
similar to that described previously in Recipe 7.8 . The hostname values have a maximum
of three segments, from which the pieces can be extracted left to right like this:

SUBSTRING_INDEX(SUBSTRING_INDEX(name,'.',-3),'.',1)
SUBSTRING_INDEX(SUBSTRING_INDEX(name,'.',-2),'.',1)
SUBSTRING_INDEX(name,'.',-1)
These expressions work properly as long as all the hostnames have three components.
But if a name has fewer than three, you don't get the correct result, as the following
query demonstrates:

mysql> SELECT name,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(name,'.',-3),'.',1) AS leftmost,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(name,'.',-2),'.',1) AS middle,
-> SUBSTRING_INDEX(name,'.',-1) AS rightmost
-> FROM hostname;