Please try this and let me know if this works

select iif(isAlpha(number),number + space(5), padl(allt(number),5," "));

as xx, number ;

from test;

order by xx

Note 1. This creates xx column for indexing.

2. Numbers will be ordered not as alphabets are but ("10" > "2")

3. Alphabets will be in alphabetical order ("B" > "ABCDEFG") or

you may use the same proc as for numbers

4. Change length (5) to max length of items

Good luck

*> > > Hi All,*

> > >

> > > In a character field numeric and Character values are stored.

> > >

> > > The following query

> > > > > > Select number from test order by number
> > >

> > >

> > > gives Output like this

> > > 1

> > > 10

> > > 2

> > > 3

> > > A etc...

> > >

> > > How to get the output like this

> > > 1

> > > 2

> > > 3

> > > 10

> > > 11

> > > A etc., More over If I use PADL A is coming after 3.

> > >

> > > Thanks

> > >

> > > R.HARI HARAN

> >

> > SELECT VAL(number) as nOrder, ;

> > number ;

> > FROM test ;

> > ORDER BY 1

> >

> > Cheers,

> > Jamie

> > jamie.osborn@foxite.com

>

> Two things: the sort order should have numbers first in numerical order, letters last in alphabetical order, right? But VAL(letters) will be zero, so letters will come first, and with only one ORDER, they'll be unalphabetized. You need to do this:

>

> > CALCULATE max(val(number)) FROM test TO nMax && Or just "nMax = 9999" if you know an upper bound
> SELECT MOD(nMax + val(number)), nMax + 1) as nOrder, test.* FROM test ORDER BY nOrder, number
>