Order a column in MySQL with IP Address records?

For this, use INET_ATON() in MySQL. The INET_ATON() method would allow a user to convert IP Address records to a number and then we can use ORDER BY to order them.

Let us first create a table −

mysql> create table DemoTable
   -> (
   -> IpAddress varchar(50)
   -> );
Query OK, 0 rows affected (1.36 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('192.168.110.78');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable values('192.168.110.87');
Query OK, 1 row affected (0.27 sec)
mysql> insert into DemoTable values('192.168.110.75');
Query OK, 1 row affected (0.26 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+----------------+
|       IpAdress |
+----------------+
| 192.168.110.78 |
| 192.168.110.87 |
| 192.168.110.75 |
+----------------+
3 rows in set (0.00 sec)

Here is the query to order by IP Address records −

mysql> select *from DemoTable
   -> order by inet_aton(IpAddress);

This will produce the following output −

+----------------+
|      IpAddress |
+----------------+
| 192.168.110.75 |
| 192.168.110.78 |
| 192.168.110.87 |
+----------------+
3 rows in set (0.00 sec)

AmitDiwan

Published on 12-Dec-2019 10:03:38