SQL Server: Getting numbers in (right) order

Hi folks,

I have a database full of documents and each section within a document
is given an ID like "1.0", "1.1", "1.2" and so on. The datatype is
varchar (I wasn't sure if a better type exists?).

Now this is fine until I get to "1.10" which is then logically between
"1.1" and "1.2" but obviously, in the document it should be after "1.9".
Is there any way I can retrieve data from the database using this sort
of /logical to me, but not to a computer/ manner?

Just in case I am making no sense (which is quite likely); at the moment
I am using something like:

"SELECT id FROM sections ORDER BY id ASC"

Which returns:

1.1, 1.10, 1.11, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9

Whereas I would like to get it in the order:

1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9, 1.10. 1.11

Is there something magical I can use to achieve this?

Cheers,

--
Dylan Parry
http://webpageworkshop.co.uk -- FREE Web tutorials and references

Carved in mystic runes upon the very living rock, the last words of
Dylan Parry of alt.www.webmaster make plain:

> I have a database full of documents and each section within a document
> is given an ID like "1.0", "1.1", "1.2" and so on. The datatype is
> varchar (I wasn't sure if a better type exists?).
>
> Now this is fine until I get to "1.10" which is then logically between
> "1.1" and "1.2" but obviously, in the document it should be after
> "1.9". Is there any way I can retrieve data from the database using
> this sort of /logical to me, but not to a computer/ manner?

If SQL Server has the CAST() function and the DECIMAL type, you could
try that:

order by cast(id as DECIMAL);

--
Alan Little
Phorm PHP Form Processor
http://www.phorm.com/

And lo, Dylan Parry didst speak in alt.www.webmaster:

> Just in case I am making no sense (which is quite likely); at the moment
> I am using something like:
>
> "SELECT id FROM sections ORDER BY id ASC"
>
> Which returns:
>
> 1.1, 1.10, 1.11, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9
>
> Whereas I would like to get it in the order:
>
> 1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9, 1.10. 1.11
>
> Is there something magical I can use to achieve this?

Would this work?

"SELECT id FROM sections ORDER BY LENGTH(id) ASC, id ASC"

Grey

--
The technical axiom that nothing is impossible sinisterly implies the
pitfall corollory that nothing is ridiculous.
- http://www.greywyvern.com/orca#ring - Orca Ringmaker: Host a webring
from your website!

Create a function that parses the string into two halves. Pre-decimal
half, and post decimal half.

Here's some pseudo code;

sLeft=left(sInput,at('.',sInput)
sRight=substr(sInput,at('.',sInput)+1
iValue=StrToInt(sLeft)*100+StrToInt(sRight)

return iValue


On Fri, 14 Oct 2005 15:20:30 +0100, Dylan
Parry wrote:

> Hi folks,
>
> I have a database full of documents and each section within a document is
> given an ID like "1.0", "1.1", "1.2" and so on. The datatype is varchar (I
> wasn't sure if a better type exists?).
>
> Now this is fine until I get to "1.10" which is then logically between
> "1.1" and "1.2" but obviously, in the document it should be after "1.9".
> Is there any way I can retrieve data from the database using this sort of
> /logical to me, but not to a computer/ manner?
>
> Just in case I am making no sense (which is quite likely); at the moment I
> am using something like:
>
> "SELECT id FROM sections ORDER BY id ASC"
>
> Which returns:
>
> 1.1, 1.10, 1.11, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9
>
> Whereas I would like to get it in the order:
>
> 1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9, 1.10. 1.11
>
> Is there something magical I can use to achieve this?
>
> Cheers,

--
George Sexton
MH Software, Inc. - Home of Connect Daily Web Calendar
http://www.mhsoftware.com/conectdaily.htm

"Dylan Parry" <usenet@dylanparry.com> wrote in message
news:nucbaci7fn1r$.dlg@dylanparry.com...
> Hi folks,
>
> I have a database full of documents and each section within a document
> is given an ID like "1.0", "1.1", "1.2" and so on. The datatype is
> varchar (I wasn't sure if a better type exists?).
>
> Now this is fine until I get to "1.10" which is then logically between
> "1.1" and "1.2" but obviously, in the document it should be after "1.9".
> Is there any way I can retrieve data from the database using this sort
> of /logical to me, but not to a computer/ manner?
>
> Just in case I am making no sense (which is quite likely); at the moment
> I am using something like:
>
> "SELECT id FROM sections ORDER BY id ASC"
>
> Which returns:
>
> 1.1, 1.10, 1.11, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9
>
> Whereas I would like to get it in the order:
>
> 1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9, 1.10. 1.11
>
> Is there something magical I can use to achieve this?
>
> Cheers,
>
> --
> Dylan Parry


I would add three numeric fields and store numeric values there as parts of
the entire ID
For example
ID1 = 1
ID2 = 1
ID3 = 2
would give us 1.1.2
Select query will look like
select * from sections order by ID1,ID2,ID3

Vlad
http://wowdealz.com

On Sat, 15 Oct 2005 00:26:15 +0100, Dylan Parry <usenet@dylanparry.com>
wrote:

>If only changing the database was an option!

It is. Make a scratch table with the separated section numbering and put
a foreign key from it into the "data" table.