Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

ORDER BY IpAddress

4 posts in General Discussion Last posting was on 2004-01-27 09:39:51.0Z
Vikram Rao Posted on 2004-01-24 02:17:50.0Z
From: Vikram Rao <vikram@nospam.cisco.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: ORDER BY IpAddress
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: dhcp-171-69-125-217.cisco.com
X-Original-NNTP-Posting-Host: dhcp-171-69-125-217.cisco.com
Message-ID: <4011d5ce$1@forums-1-dub>
Date: 23 Jan 2004 18:17:50 -0800
X-Trace: forums-1-dub 1074910670 171.69.125.217 (23 Jan 2004 18:17:50 -0800)
X-Original-Trace: 23 Jan 2004 18:17:50 -0800, dhcp-171-69-125-217.cisco.com
Lines: 11
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2384
Article PK: 4588

Hi,

I am currently using a VARCHAR(15) to store IP addresses in a table. The
"ORDER BY" clause doesn't work correctly on IPAddresses. For
example: 10.77.202.233 shows up before 10.77.202.92 in the result set
when it should be the other way around. How do I resolve this issue?

Thanks!

-Vikram


Breck Carter [TeamSybase] Posted on 2004-01-25 16:56:41.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: ORDER BY IpAddress
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <n4s7109scbai2clr1824p9a4p0gugbgr2i@4ax.com>
References: <4011d5ce$1@forums-1-dub>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
X-Original-Trace: 25 Jan 2004 09:06:36 -0800, bcarter.sentex.ca
Lines: 29
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 25 Jan 2004 08:40:24 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 25 Jan 2004 08:56:41 -0800
X-Trace: forums-1-dub 1075049801 10.22.108.75 (25 Jan 2004 08:56:41 -0800)
X-Original-Trace: 25 Jan 2004 08:56:41 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2387
Article PK: 4591

The ORDER BY is working just fine; the character '2' sorts ahead of
the character '9'. If you want to sort it on the separate numeric
values instead of one big string, you will have to separate the 4
components, and either convert them to numeric or right-justify them
as 3-character strings. Another alternative is to insert leading
blanks or zeros in each component, as in '010.077.202.233', for the
purposes of the ORDER BY. Note that you can specify an expression in
the ORDER BY.

Breck

On 23 Jan 2004 18:17:50 -0800, Vikram Rao <vikram@nospam.cisco.com>

wrote:

>Hi,
>
>I am currently using a VARCHAR(15) to store IP addresses in a table. The
> "ORDER BY" clause doesn't work correctly on IPAddresses. For
>example: 10.77.202.233 shows up before 10.77.202.92 in the result set
>when it should be the other way around. How do I resolve this issue?
>
>Thanks!
>
> -Vikram

--
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com


Richard Biffl Posted on 2004-01-26 00:27:19.0Z
From: "Richard Biffl" <sybaseforums@removvethiisphrraseblacklettersoftware.com>
Newsgroups: ianywhere.public.general
References: <4011d5ce$1@forums-1-dub>
Subject: Re: ORDER BY IpAddress
Lines: 18
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: pcp03078470pcs.hyatsv01.md.comcast.net
X-Original-NNTP-Posting-Host: pcp03078470pcs.hyatsv01.md.comcast.net
Message-ID: <40145ee7$1@forums-1-dub>
Date: 25 Jan 2004 16:27:19 -0800
X-Trace: forums-1-dub 1075076839 68.48.162.78 (25 Jan 2004 16:27:19 -0800)
X-Original-Trace: 25 Jan 2004 16:27:19 -0800, pcp03078470pcs.hyatsv01.md.comcast.net
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2388
Article PK: 4592

Besides Breck's suggestions, you could store the 4 octets of the IP address
as an UNSIGNED INTEGER in the database, and create functions in the database
to convert to and from the customary string format for display and data
entry. It might be simpler though to store the string as-is and create a
function like IP2Uint(IN IPStr CHAR(15)) RETURNS UNSIGNED INTEGER that
converts the string to an unsigned integer, so you can ORDER BY
IP2Uint(IPAddress).

Richard

"Vikram Rao" <vikram@nospam.cisco.com> wrote in message
news:4011d5ce$1@forums-1-dub...
> I am currently using a VARCHAR(15) to store IP addresses in a table. The
> "ORDER BY" clause doesn't work correctly on IPAddresses. For
> example: 10.77.202.233 shows up before 10.77.202.92 in the result set
> when it should be the other way around. How do I resolve this issue?


lloyd Posted on 2004-01-27 09:39:51.0Z
From: "lloyd" <lbryant@astra-games.com>
Newsgroups: ianywhere.public.general
References: <4011d5ce$1@forums-1-dub>
Subject: Re: ORDER BY IpAddress
Lines: 22
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
NNTP-Posting-Host: 213.107.199.162
X-Original-NNTP-Posting-Host: 213.107.199.162
Message-ID: <401631e7$1@forums-1-dub>
Date: 27 Jan 2004 01:39:51 -0800
X-Trace: forums-1-dub 1075196391 213.107.199.162 (27 Jan 2004 01:39:51 -0800)
X-Original-Trace: 27 Jan 2004 01:39:51 -0800, 213.107.199.162
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2394
Article PK: 4599

Or just seperate each part of the IP address into 4 seperate unsigned bytes
and ORDER BY the 4 fields. To display just concatenate the 4 bytes seperated
by .'s

Lloyd

"Vikram Rao" <vikram@nospam.cisco.com> wrote in message
news:4011d5ce$1@forums-1-dub...
> Hi,
>
> I am currently using a VARCHAR(15) to store IP addresses in a table. The
> "ORDER BY" clause doesn't work correctly on IPAddresses. For
> example: 10.77.202.233 shows up before 10.77.202.92 in the result set
> when it should be the other way around. How do I resolve this issue?
>
> Thanks!
>
> -Vikram
>