--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:

>
> > snip
>
> It is contrary to common logic, not just Firebird design. I would say
> that Paradox is on its own there. It is like saying that 'Zebra' comes
> before 'Antelope' because it is shorter.

You're quite right. I went back and double-checked Paradox on this,
and it works just a FB does. I must have been remembering the sorting
was no problem...

The column can have alphanumeric values, otherwise I would have used a
numeric datatype.

Thanks also to Milan and Woody. I will try some things with CAST, etc.
I think there is only one or two places where the sort order will be a
problem for the user [eg. a printed listing] and I can 'fix' the order
using code in the app.

Dan

>
> If the field is indeed a varchar, and in the absence of other
> charactersets and collations, sorting is pretty simple.
>
> Assuming the two values:
>
> 999
> 1000
>
> Does '1' come before '9', well yes CHR(49) is smaller than CHR(57),
> and therefore a string starting with '1' is defined to be smaller than
> a string starting with '9'.
>
> The problem is that you wish it to sort numerically not
> alphabetically. It is pretty simple to do this by casting the field in
> the order by clause as an integer. The downside to this is that it
> will not work if your field also contains strings that can not be
> converted to an integer. Unfortunately, the ordering by string length
> first function won't work either. It will not give you an exception
> like casting, but you will end up with 'Zebra' before 'Antelope'.
>
> Maybe you can create a custom collation, I have never done that though.
>
> Adam
>