[thelist] sorting question

[rudy]

>...  a similar method could certainly be used for MS SQL Server.
> Just look for a function which will truncate the decimal place off
> of a number.

that'd be the floor() mathematical function

sadly, it is a poor choice in this instance, as them aren't numbers to
start with, joel said them are "accounting thingies" and therefore probably
strings

paul's suggestion hinges on the convert() function turning strings into
numbers, which is awkward and might perform poorly, but i have no reason to
think it won't actually work

another method works only with strings --

split the thingie string using charindex to find the decimal point

left side is

    substring( accno, 1, charindex('.',accno)-1 )

and that's your major sort field

right side is

   substring( accno, charindex('.',accno)+1
          , len(accno)-charindex('.',accno) )

pad the right side with, oh, let's say five zeroes to the left, then take
the rightmost five-char substring

   right( '00000' + substring(accno,charindex('.',accno)+1
                                     , len(accno)-charindex('.',accno))
             , 5)

and that's your minor sort field

caution:  untested  -- you might have to adjust +1 or -1   ;o)


> sorry i can't be of more help with MS SQL Server. Not my strongpoint.

me neither, but i have a release 6 textbook that stands me in good stead

also, there's BOL (books online), available as a free (~38meg) download,
but it's upstairs on the kids' computer and they're asleep...

rudy