Re: Question about sql-query to get IP-addresses

If you convert that part of the address to numeric with CAST, it will sort
properly.
For example:

select substr(A1.ADDRESS,1,posstr(address,':')-1) as IP_address,
cast(substr(A1.ADDRESS,12,posstr(address,':')-12) as numeric) as tailer,
A1.entity as Name from summary A1, nodes A2
where A1.entity=A2.node_name AND
node_name in
(select node_name from ASSOCIATIONS) AND
start_time>timestamp(concat('2002-11-26',' 07:00'))
order by tailer  ASC

Now this particular code assumes that ALL your IP addresses are like those
in your example, "1xx.25.yyy.zzz"
If you have any addresses where the 1st 3 nodes of the address are not
exactly 11 characters long, you will need to be sneakier about how you split
it up...  but you get the idea.


-----Original Message-----
From: brian welsh [mailto:brianwelsh3 AT HOTMAIL DOT COM]
Sent: Wednesday, November 27, 2002 1:50 PM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Question about sql-query to get IP-addresses


Hello,

Question about a sql-query to get IP-addresses from client-nodes associated
with a schedule:

select substr(A1.ADDRESS,1,posstr(address,':')-1) as IP_address,A1.entity as
Name from summary A1, nodes A2 where A1.entity=A2.node_name AND node_name in
(select node_name
from ASSOCIATIONS) AND start_time>timestamp(concat('2002-11-26',' 07:00'))
order by IP_address ASC


Query gives output like this:
IP_ADDRESS      NAME
------------------      ------------------
1xx.25.141.20           NT_A
1xx.25.141.21           AIX_Z
1xx.25.141.5            AIX_B
1xx.25.141.6            NT_X
1xx.25.141.7            NT_D
1xx.25.141.8            W2K_F
1xx.25.141.9            W2K_G
1xx.25.143.118          W2K_A
1xx.25.143.21           AIX_S
1xx.25.143.22           AIX_M
1xx.25.143.23           NT_K

I would like to order by IP-address on last octet of IP-address, like
sequence 5, 6, 7, 8, 9, 20 and so on and not like 20, 21, 5, 6, 7

Is there someone who can give me a hint?

Any tip is welcome.

Thanx,

Brian.






_________________________________________________________________
Chatten met je online vrienden via MSN Messenger. http://messenger.msn.nl/