[AccessD] Sorting Alpha-Numerics

This should be a touch faster than Gustav's as it only uses one function
call vs. five.

SELECT Table1.TT
FROM Table1
ORDER BY IsNumeric([tt]), Table1.TT;

Here's the (abridged) output...

000
000
001
001
002
002
003
004
005
006
007
008
009
010
011
012
...
995
996
997
998
999
1A1
1a1
1B2
a23
ZZZ

Lambert

> -----Original Message-----
> From:	Mitsules, Mark [SMTP:Mark.Mitsules at ngc.com]
> Sent:	Tuesday, October 21, 2003 12:13 PM
> To:	'Access Developers discussion and problem solving'
> Subject:	RE: [AccessD] Sorting Alpha-Numerics
> 
> Holy cow...I'm in awe.  I remember a quote from this list a long time ago
> that seems appropriate at the moment.  It read something like "Gustav is a
> legend...cha cha cha"
> 
> Thank you sir,
> 
> 
> Mark
> 
> 
> 
> -----Original Message-----
> From: Gustav Brock [mailto:gustav at cactus.dk] 
> Sent: Tuesday, October 21, 2003 11:57 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Sorting Alpha-Numerics
> 
> 
> Hi Mark
> 
> Oh yeah, one more go to format leading zeroes:
> 
> .. Abs(StrComp(strVal, Right("00" & CStr(Val(strVal)), 3), 1))
> 
> /gustav
> 
> 
> >> Order By Abs(StrComp(strVal, Cstr(Val(strVal)), 1)) ASC, strVal ASC
> 
> > Hmm...on this iteration 100-999 sorted properly first, the remaining 
> > records reverted back to the way it was before...
> 
> > 001
> > .
> > 009
> > 00A
> > .
> > 00Z
> > 010
> > .
> > Etc...
> 
> 
> 
> > Mark
> 
> 
> > -----Original Message-----
> > From: Gustav Brock [mailto:gustav at cactus.dk]
> > Sent: Tuesday, October 21, 2003 11:11 AM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Sorting Alpha-Numerics
> 
> 
> > Hi Mark
> 
> >> Oooh...that is a little closer Gustav.  You must be on the right 
> >> track. That approach put 001 through 019 in order before jumping to 
> >> 01A.
> 
> > Of course ... StrComp returns -1 for that 01A.
> 
> > This should do:
> 
> >   Order By Abs(StrComp(strVal, Cstr(Val(strVal)), 1)) ASC, strVal ASC
> 
> > /gustav
> 
> 
> >> You could do this SQL
> 
> >>  .. Order By StrComp(strVal, Cstr(Val(strVal)), 1) ASC, strVal ASC
> 
> >> /gustav
> 
> 
> >>> Greetings,
> 
> >>> I have a text field containing all possible 3 digit alpha-numeric
> >>> combinations from 000 to ZZZ.  I am looking for a method to sort this 
> >>> text field where 000 through 999 are listed in numerical order before 
> >>> the remaining alpha-numeric combinations are listed in ASCII sort 
> >>> order.  Any suggestions?  I have tried to do this in Excel, however, 
> >>> Excel makes some startling decisions on its own...such as turning 7E9 
> >>> into 7000000000.
> 
> >>> Example:
> >>> 000
> >>> 001
> >>> .
> >>> .
> >>> 998
> >>> 999
> >>> 00A
> >>> 00B
> >>> .
> >>> .
> >>> ZZZ
> 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com