CODEWITHSUNDEEP
mysqlsql
keijeizei
keijeizei

Reputation: 80

How do I get the maximum of a varchar column

Let's say I have a table with all the possible IP addresses

id ip_address
1 0.0.0.0
2 0.0.0.1
... ...
x 255.255.255.255

(let's just say x is a large number)

When I do MAX(ip_address), the output is 99.99.99.99, and not 255.255.255.255

Other questions I found on stackoverflow has a solution where they will output the id with the maximum ip_address, but will not ouput the proper ip_address which is 255.255.255.255 (they use INET_ATON() which results to a long string of number like 4292247153. I want the output to show something like

id MAX(ip_address)
x 255.255.255.255

Upvotes: 0

Views: 149

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

I am adding an answer here in order to show that when writing SQL queries you should know your data :-)

There ain't such a thing as a maximum IP address. They are addresses. They are different but not lower or higher than another. Just like real addresses, where none of your friends lives in the "minimum" or "maximum" address.

What you are takling about is the maximum IP address number. And the problem with that is: you don't store that number in your database. What you are storing is their IPv4 string representation. When the number is 4294967295 (or hex FFFFFFFF), you are storing '255.255.255.255', when the number is 1667457891 (hex 63636363) you are storing '99.99.99.99'. You could even store invalid IP address numbers, such as '123.456.789.000' or 'this is my IP address', if you have no check constraints enabled.

While it is uncommon to be interested in a minimum or maximum IP address number, wanting to order the numbers is quite common. The cleanest approach would be to store the mere 32 bit numbers and convert them to an IPv4 string, e.g. in a computed column. Then you could just sort them numerically.

If you choose a different approach, say, to store the four bytes separately or to store the IPv4 string, then you must care about data validity (check constraints) and sorting (via a stored function or just manually by splitting the string as shown in Gordon's answer) yourself. As sorting is something you may need in more than just one query, you would probably write a stored function that you can call like select * from ip_addresses order by sortkey_for_ipv4(ip_address).

Luckily, MySQL has this function built in, as you already know. Hence:

select ip_address
from ip_addresses
order by inet_aton(ip_address) desc 
limit 1;

(Also already mentioned in Gordon's answer.)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This is a pain in MySQL, but you can break the ip address into parts and order each substring separately to maximize that value:

select id
from t
order by length(substring_index(ip_address, '.', 1) desc,
         substring_index(ip_address, '.', 1) desc,
         length(substring_index(ip_address, '.', 2) desc,
         substring_index(ip_address, '.', 2) desc,
         length(substring_index(ip_address, '.', 3) desc,
         substring_index(ip_address, '.', 3) desc,
         ip_address desc;

Or, you can use the inet_ functions:

select i.*
from t
order by inet_aton(ip_address) desc
limit 1;

Upvotes: 2

Vendec
Vendec

Reputation: 83

It is possible to use a sub-query. The equivalent sentence would be "select all the lines where the INET-ATON value is equal to the highest INET-ATON value in the table".

SELECT *, INET_ATON(ip_address) max_ip
FROM ip_table
WHERE INET_ATON(ip_address) = (
   SELECT MAX(INET_ATON(ip_address))
   FROM ip_table
)

Upvotes: 0

Related Questions