order by.... numbers? Help me sort ip addresses

Question

MySQL, Trying to get a list of ip addresses, in order.

this query

select ip from sn_192_168_0 

gives this

192.168.0.1
192.168.0.10
192.168.0.100
192.168.0.101

We want

192.168.0.1
192.168.0.2
...snip..
192.168.0.10

Answer 1

Try the INET_ATON function

SELECT ip FROM sn_192_168_0
ORDER BY INET_ATON(ip);

Give it a Try !!!

CAVEAT : It is best not to store the INET_ATON values. There are some past quirks with this function is you have invalid numbers between dots and calling it in triggers.

• http://bugs.mysql.com/bug.php?id=5448
• http://bugs.mysql.com/bug.php?id=39386

These bugs are cleaned up now.

Short IP addresses are handled properly. Here is an example from MySQL 5.5.12 in Windows 7

mysql> SELECT INET_ATON('127.0.0.1'), INET_ATON('127.1');
+------------------------+--------------------+
| INET_ATON('127.0.0.1') | INET_ATON('127.1') |
+------------------------+--------------------+
|             2130706433 |         2130706433 |
+------------------------+--------------------+
1 row in set (0.05 sec)