[thelist] sorting question

[Warden, Matt]

On Feb 19, JCanfield at magisnetworks.com had something to say about [thelist]...

>We're pulling some numbers from an MS-SQL db in the format 3.1, 3.2, . . .
>3.9, 3.10, 3.11, 3.12 etc.
>
>I need to sort them so that 3.2 comes before 3.12 (that is, three point two
>comes before three point twelve.) They're accounting thingies, rather than
>plain old decimals.
>
>Is there a way to parse, sort, and concatenate within SQL? Any other ideas?

order by TRUNCATE(field,0), ((field - TRUNCATE(field,0)) *100)

basically says:

order by integer, then order by (subtract the integer from the number,
leaving the decimal, then multiply by 100)

which says:

order by integer, then order by the fractional part represented as an
integer.


so


3,  0  (3.0)
3,  2  (3.2)
3, 12  (3.12)
4,  2  (4.2)
etc.

that's what you wanted, right?

if you're going to have decimals with more than 2 decimal places, you'll
want to increase 100 to a greater number. i'm assuming there is some
system where this is like a chapter 3, section 12 kind of deal.

as far as i know, this is the only way to do this without converting it to
a string and parsing out the decimal portion.



--
mattwarden
mattwarden.com