CODEWITHSUNDEEP
mysql
AaronJAnderson
AaronJAnderson

Reputation: 1724

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

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

Upvotes: 8

Views: 5239

Answers (2)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44363

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.

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)

Upvotes: 19

redmoon7777
redmoon7777

Reputation: 4526

you can use 

SELECT ip FROM sn_192_168_0 ORDER BY LPAD(  ip, 16, 0 )

the number 16 is the max length of the ip

Upvotes: 0

Related Questions