[RESOLVED] Need query

arafin

Hi,

I have table named ip_addr which has a column ip that stores IP addresses such as
1) 1.1.1.10
2) 1.1.2.11
3) 1.1.1.100
4) 1.1.1.16
5) 1.1.1.12
6) 3.1.1.14
7) 2.1.1.10

I want to display it like this

1) 1.1.1.10
2) 1.1.2.11
3) 1.1.1.12
4) 1.1.1.16
5) 1.1.1.100
6) 2.1.1.10
7) 3.1.1.14

What is the query ?

In a word, I need a query that will retrieve ip addresses in ascending order of ip column regarding ascding values of . in ip column from left to right ( ex 1.1.2.1 will come later than 1.1.1.10)

AIS4U

arafin:

Isn't is just:

SELECT ip
FROM ip_addr
ORDER BY ip asc;

I think this should work.

Good Luck

nemonoman

No, the sort is extremely complex.

You want to find the individual substrings of the IP address, and then sort them numerically by substring.

ORDER BY
SUBSTRING_INDEX( ip , '.',1 )1 ,
SUBSTRING_INDEX( SUBSTRING_INDEX( ip , '.',2 ) , '.',-1 ) 1,
SUBSTRING_INDEX( SUBSTRING_INDEX( ip , '.',3) , '.',-1 ) 1,
SUBSTRING_INDEX( SUBSTRING_INDEX( ip , '.',4 ) , '.',-1 ) 1

tomhath

What you want is called a "natural sort".

http://us2.php.net/natsort

arafin

Thanks nemonoman. It works.

tomhath, I think your natural sort will help me think.

AIS4U , Thank you.