How could I make a text field sort in the following way?

2CBE00001 2CBE00001
2CBE00002 2CBE01001
2CBE00003 2CBE02001
2CBE01001 2CBE00002
2CBE01002 2CBE01002
2CBE01003 2CBE02002
2CBE02001 2CBE00003
2CBE02002 2CBE01003
2CBE02003 2CBE02003

Any help would be greatly appreciated
Thanks
Dave

Dave,

If characters 1 through 5 and 7 through 8 are truly constant then this will
do the sort.

SELECT Field1 FROM Table1 ORDER BY Mid([Field1],9,1), Mid([Field1],6,1);

Linda


"RR" <dlanham747@prodigy.net> wrote in message
news:4q4Bc.370$DI4.210@newssvr32.news.prodigy.com. ..[color=blue]
> How could I make a text field sort in the following way?
>
> 2CBE00001 2CBE00001
> 2CBE00002 2CBE01001
> 2CBE00003 2CBE02001
> 2CBE01001 2CBE00002
> 2CBE01002 2CBE01002
> 2CBE01003 2CBE02002
> 2CBE02001 2CBE00003
> 2CBE02002 2CBE01003
> 2CBE02003 2CBE02003
>
> Any help would be greatly appreciated
> Thanks
> Dave
>
>[/color]

"RR" <dlanham747@prodigy.net> wrote in message
news:4q4Bc.370$DI4.210@newssvr32.news.prodigy.com. ..[color=blue]
> How could I make a text field sort in the following way?
>
> 2CBE00001 2CBE00001
> 2CBE00002 2CBE01001
> 2CBE00003 2CBE02001
> 2CBE01001 2CBE00002
> 2CBE01002 2CBE01002
> 2CBE01003 2CBE02002
> 2CBE02001 2CBE00003
> 2CBE02002 2CBE01003
> 2CBE02003 2CBE02003
>
> Any help would be greatly appreciated
> Thanks
> Dave[/color]

Dave, you haven't made what you hope to accomplish very clear. Is it that
you want it sorted by the last four characters in reverse sequence? Or,
perhaps the last 5?

Maybe it's really a problem in pattern recognition?

"Randy Harris" <randy@SpamFree.com> wrote in message
news:mCaBc.26066$eH1.12255631@newssvr28.news.prodi gy.com...[color=blue]
>
> "RR" <dlanham747@prodigy.net> wrote in message
> news:4q4Bc.370$DI4.210@newssvr32.news.prodigy.com. ..[color=green]
> > How could I make a text field sort in the following way?
> >
> > 2CBE00001 2CBE00001
> > 2CBE00002 2CBE01001
> > 2CBE00003 2CBE02001
> > 2CBE01001 2CBE00002
> > 2CBE01002 2CBE01002
> > 2CBE01003 2CBE02002
> > 2CBE02001 2CBE00003
> > 2CBE02002 2CBE01003
> > 2CBE02003 2CBE02003
> >
> > Any help would be greatly appreciated
> > Thanks
> > Dave[/color]
>
> Dave, you haven't made what you hope to accomplish very clear. Is it that
> you want it sorted by the last four characters in reverse sequence? Or,
> perhaps the last 5?
>
>[/color]

Squirrel wrote:
[color=blue]
> Dave,
>
> If characters 1 through 5 and 7 through 8 are truly constant then this will
> do the sort.
>
> SELECT Field1 FROM Table1 ORDER BY Mid([Field1],9,1), Mid([Field1],6,1);
>
> Linda
>
>
> "RR" <dlanham747@prodigy.net> wrote in message
> news:4q4Bc.370$DI4.210@newssvr32.news.prodigy.com. ..
>[color=green]
>>How could I make a text field sort in the following way?
>>
>>2CBE00001 2CBE00001
>>2CBE00002 2CBE01001
>>2CBE00003 2CBE02001
>>2CBE01001 2CBE00002
>>2CBE01002 2CBE01002
>>2CBE01003 2CBE02002
>>2CBE02001 2CBE00003
>>2CBE02002 2CBE01003
>>2CBE02003 2CBE02003
>>
>>Any help would be greatly appreciated
>>Thanks
>>Dave
>>
>>[/color]
>
>
>[/color]


Or....

SELECT Field1 FROM Table1 ORDER BY Right([fieldname],1) ASC,
Mid([Field1],6,1) ASC