Hi,

> > Actually - WHO decides that this would be the right order:
> >
> > 1
> > 11
> > 20
> > A
> > AA
> > BA
> >
> > ??
> >
> > What kind of order is this? This is a very custom order...
>
> I think "the right order" is what the user/programmer needs at a
> certain point in the application, so it varies from case to case.
>
> Todd wants a numerical sorting in a char column that may also contain
> non-numerical values. I'm sure he'll have his reasons, so that is
> the right order for this query. Or maybe we should say: the ideal
> ordering. Next question of course is: how to achieve it? But it seems
> he's already done that too.
>
> BTW: it was even a little more complicated. I thought he wanted an
> ordering like you showed above. But there may also be values like
> '23B' and he wants them aligned between the real numerals:
>
> 1
> 3
> 3B
> 10
> 12
> 12P9
> 20
> A
> AA

Sounds like writing a custom collation then :)

This really is a weird order. Perhaps you can UDF the select
and order by UDF(mycol) that returns a certain numerical value.


With regards,

Martijn Tonies
Database Workbench - the developer tool for InterBase & Firebird
Upscene Productions
http://www.upscene.com