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 05:33:38

• Related Questions & Answers
• Difference between Static IP Address and Dynamic IP Address
• MySQL string manipulation to count only sub-part of duplicate values in IP ADDRESS records?
• Alias/Secondary IP Address
• Difference between MAC Address and IP Address
• Difference between IP Address and MAC Address
• Validate IP Address in C#
• Validate IP Address in C++
• Validate IP Address in Python
• Find records with double quotes in a MySQL column?
• How to order records by a column in MySQL and place empty records at the end?
• MySQL query to copy IP address from varchar column to integer in the same table?
• Defanging an IP Address in Python
• Order randomly in MySQL with a random value column?
• Find records with a specific last digit in column with MySQL
• Displaying only a list of records in ASC order with MySQL