Sorting Query Results - MySQL - page 261

| jakarta.apache.org |
| dbi.perl.org |
+--------------------+
If your hostnames have a maximum of four segments rather than three, add to the ORDER
BY clause another SUBSTRING_INDEX() expression that adds three dots at the beginning
of the hostname values.
7.10. Sorting Dotted-Quad IP Values in Numeric Order
Problem
You want to sort in numeric order strings that represent IP numbers.
Solution
Break apart the strings, and sort the pieces numerically. Or just use INET_ATON() . Or
consider storing the values as numbers instead.
Discussion
If a table contains IP numbers represented as strings in dotted-quad notation
( 192.168.1.10 ), they sort lexically rather than numerically. To produce a numeric or‐
dering instead, sort them as four-part values with each part sorted numerically. Or, to
be more efficient, represent the IP numbers as 32-bit unsigned integers, which take less
space and can be ordered by a simple numeric sort. This section shows both methods.
To sort string-valued dotted-quad IP numbers, use a technique similar to that for sorting
hostnames (see Recipe 7.9 ), but with the following differences:
• Dotted quads always have four segments. There's no need to add dots to the value
before extracting substrings.
• Dotted quads sort left to right. The order of the substrings used in the ORDER BY
clause is opposite to that used for hostname sorting.
• The segments of dotted-quad values are numbers. Add zero to each substring to
force a numeric rather than lexical sort.
Suppose that a hostip table has a string-valued ip column containing IP numbers:
mysql> SELECT ip FROM hostip ORDER BY ip;
+-----------------+
| ip |
+-----------------+
| 127.0.0.1 |
| 192.168.0.10 |
| 192.168.0.2 |