[Fwd: Re: Order By Clause]

Erich Dollansky wrote:

>
>> I need to come up with a way of ordering a varchar field of the form
>> 1.x.x.x numerically .
>> The number of fields after the x may be dynamic (0 or more).
>> So for instance I may have :
>> 1.0
>> 1.01
>> 1.10
>> 1.101
>> 2.0
>> 2.01
>> 2.2
>> 11.0
>> 20.0
>>
>> Does anyone have any idea how I might be able to do this?
>>
> It does not work as long as you do not right-align the data.
>
> Your problem is that you do not know the length of a VARCHAR field.
>
> Can you change the design to a fixed field length?
>
Hi Erich,
Thanks for answering.
I can make it a fixed field but the numbering system is still variable (
1.0,1.01,1.001,1.1.0,1.2.1 etc).
How would this help?
They're chapter numbers for books .

Mark

--

Regards,

*Mark*





--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: [Fwd: Re: Order By Clause]

------=_NextPart_000_00D0_01C52ADA.2B8830F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

This may be incorrect but if they are Chapters for books, then there will be
very little Data to them. Why not just separate them into individual
columns. I.E. (1.001.02.14) could become

C1 C2 C3 C4
1 001 02 14

Then just do a SELECT with ORDER BY C1 ASC, C2 ASC, C3 ASC, C4 ASC

I am not an expert so this may be against some normalization rules if so
please let me know

Warren

Totus possum, totum Deum.
Totus ero, totum meum.
WSW


------=_NextPart_000_00D0_01C52ADA.2B8830F0
Content-Type: message/rfc822
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment

Reply-To:
From: "Mark Love"
To: "Mysql list"
Subject: [Fwd: Re: Order By Clause]
Date: Thu, 17 Mar 2005 08:59:23 -0700
Message-ID: <4239A95B.7000009@love81.freeserve.co.uk>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2910.0)
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
List-Unsubscribe:
List-Help:
X-Relay-IP: 213.136.52.31
X-SPF-Debug: implemented=no,result=pass
X-Spam-Score: 1.614 (*) DNS_FROM_RFC_POST
X-Tracking-ID: j2HG0F5f058607
X-Scanned-By: MIMEDefang 2.45
X-AntiSpam: Checked for restricted content by Gordano's AntiSpam Software
X-Accept-Language: en-us, en
X-Virus-Checked: Checked
X-OriginalArrivalTime: 17 Mar 2005 15:59:24.0693 (UTC) FILETIME=[4A58D450:01C52B0A]
Importance: Normal
X-Enigmail-Version: 0.89.5.0
X-Enigmail-Supports: pgp-inline, pgp-mime


Erich Dollansky wrote:

>
>> I need to come up with a way of ordering a varchar field of the form
>> 1.x.x.x numerically .
>> The number of fields after the x may be dynamic (0 or more).
>> So for instance I may have :
>> 1.0
>> 1.01
>> 1.10
>> 1.101
>> 2.0
>> 2.01
>> 2.2
>> 11.0
>> 20.0
>>
>> Does anyone have any idea how I might be able to do this?
>>
> It does not work as long as you do not right-align the data.
>
> Your problem is that you do not know the length of a VARCHAR field.
>
> Can you change the design to a fixed field length?
>
Hi Erich,
Thanks for answering.
I can make it a fixed field but the numbering system is still variable (
1.0,1.01,1.001,1.1.0,1.2.1 etc).
How would this help?
They're chapter numbers for books .

Mark

--

Regards,

*Mark*





--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=warren@wyght.com



------=_NextPart_000_00D0_01C52ADA.2B8830F0
Content-Type: text/plain; charset=us-ascii

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org
------=_NextPart_000_00D0_01C52ADA.2B8830F0--

Re: [Fwd: Re: Order By Clause]

Looking at your sample data, you have a float value. But from your descript=
ion of the data, it's more like an ip address. If you have only 1 decimal p=
oint, it fairly simple to sort the varchar data numerically:

SELECT chapter+0 as chapter FROM Book ORDER BY chapter

The +0 forces the data conversion to numric data.

If your data contains, or can contain, multiple decimal points this won't w=
ork. Neither will making it a fixed-length and right justifying the data. I=
f all entries have the same number of decimal points, you can use the INET_=
ATON() function to sort.

If there is a maximum number of decimal points that your data may contain, =
Warren's suggestion of create separate fields would work.

But here is an idea for you. It seems to work for my small sample of data. =
Here's the data for a column named chapter in a table named book:

1.0
1.001
1.01
1.10
1.1.0
1.1.1.1.10
1.101
1.2.1
2.0
2.01
2.2
11.0
20.0

Here the select:

SELECT chapter =

FROM book
ORDER BY chapter+0, INET_ATON(chapter)

It doesn't make a lot of sense, but it does seem to work!

Randy Clamons
Systems Programming
Novaspace.com


> ------------Original Message------------
> From: Mark Love
> To: "Mysql list"
> Date: Thu, Mar-17-2005 9:00 AM
> Subject: [Fwd: Re: Order By Clause]
>
> =

> Erich Dollansky wrote:
> =

> >
> >> I need to come up with a way of ordering a varchar field of the form =

> =

> >> 1.x.x.x numerically .
> >> The number of fields after the x may be dynamic (0 or more).
> >> So for instance I may have :
> >> 1.0
> >> 1.01
> >> 1.10
> >> 1.101
> >> 2.0
> >> 2.01
> >> 2.2
> >> 11.0
> >> 20.0
> >>
> >> Does anyone have any idea how I might be able to do this?
> >>
> > It does not work as long as you do not right-align the data.
> >
> > Your problem is that you do not know the length of a VARCHAR field.
> >
> > Can you change the design to a fixed field length?
> >
> Hi Erich,
> Thanks for answering.
> I can make it a fixed field but the numbering system is still variable =

> ( =

> 1.0,1.01,1.001,1.1.0,1.2.1 etc).
> How would this help?
> They're chapter numbers for books .
> =

> Mark
> =

> -- =

> =

> Regards,
> =

> *Mark*


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org