CODEWITHSUNDEEP
phpsqlsqlitespiceworks
AJ Birone
AJ Birone

Reputation: 151

Best way to sort by IP addresses in SQL

I connect to my SpiceWorks database via PHP using the SQLITE3 PDO. I am trying to display a list of devices in order by IP address. Here is my current query:

SELECT name, ip_address FROM `devices` ORDER BY ip_address

The problem with this is that it organizes them weird like this:

enter image description here

Is there any easy way to fix this problem?

It is not possible for me to edit the database because it would through SpiceWorks off. I need a way to do this in SQL.

Upvotes: 4

Views: 10488

Answers (5)

Mookie1097
Mookie1097

Reputation: 1

Since I only cared about the last octet, I was able to use the very simple

SELECT name, ip_address FROM `devices`
ORDER BY CAST(substr(ip_address, 10) AS NUMERIC) DESC;

My ip had 9 characters before the last octet

Upvotes: 0

Nekkyo
Nekkyo

Reputation: 69

Have you tried INET_ATON function? This is probably a late answer, but maybe it'll help others.

SELECT name, ip_address
FROM devices
ORDER BY
INET_ATON(ip_address)

Upvotes: 6

user1440703
user1440703

Reputation:

I have implemented like this:

SELECT IP FROM iplist ORDER BY

CAST(substr(trim(IP),1,instr(trim(IP),'.')-1) AS INTEGER),  

   CAST(substr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')-1) AS INTEGER), 

        CAST(substr(substr(trim(IP),length(substr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')))+length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')))+length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')-1) AS INTEGER), 

        CAST(substr(trim(IP),length(substr(substr(trim(IP),length(substr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')))+length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')))+length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')))+ length(substr(trim(IP),1,instr(trim(IP),'.')))+length(substr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')))+1,length(trim(IP))) AS INTEGER)

Upvotes: 3

DimeCadmium
DimeCadmium

Reputation: 340

ORDER BY
CAST(substr(ip_address,1,instr(ip_address,'.')) AS NUMERIC),
CAST(substr(ip_address,instr(ip_address,'.'), instr(substr(ip_address,instr(ip_address,'.')))) AS NUMERIC),

Something like that should work. It'll be nasty though. (This should sort by the first two octets...)

Upvotes: 1

mw90
mw90

Reputation: 132

You have the fields as VARCHAR or some other character field, so it is sorting them by the first number. You need to CAST the type to a number in the order by statement.

Like this:

SELECT name, ip_address
FROM devices
ORDER BY

CAST(PARSENAME([ip_address], 4) AS INT),
CAST(PARSENAME([ip_address], 3) AS INT),
CAST(PARSENAME([ip_address], 2) AS INT),
CAST(PARSENAME([ip_address], 1) AS INT)

Just not sure if this works in SQLlite.....

Upvotes: 0

Related Questions