[thelist] sorting question

Warden, Matt mwarden at mattwarden.com
Tue Feb 19 22:32:00 CST 2002


On Feb 19, Warden, Matt had something to say about Re: [thelist] sorting...

>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.

dammit, that's not gonna work either, folks.

originally, i had a solution up there using strings, and i think you're
gonna have to use that method. this is what i had in MySQL. maybe it can
help you with your MS SQL Server task:

order by TRUNCATE(field,0), SUBSTRING_INDEX(field, ".", -1)

the latter part basically grabs everything from the right of the decimal
point. so, for 3.3, it'll grab 3. for 3.12, it'll grab 12.

disclaimer: you may have to run a conversion function on your field first
disclaimer x2: you may have to de-convert it back to a number in order to
get it to.. um.. ORDER correctly.

<tip>
Spending 20 extra seconds re-thinking your solution to a problem can save
yourself and others huge amounts of time:

1. realizing you've made a mistake
2. trying to find a correct solution (as if there is any other kind of
solution)
</tip>

sorry folks,

--
mattwarden
mattwarden.com




More information about the thelist mailing list