I'm new at SQL so I may be off base, but in traditional programming practice when you want that kind of sort (right justified) you would fill the field with preceding zeros and that would cause the sort that you are seeking. I'm sure that you could suppress the zeros on print with some sort of mask. BobJ ----- Original Message ----- From: "William McCormick" <[EMAIL PROTECTED]> To: "John Almberg" <[EMAIL PROTECTED]>; "Mysql" <[EMAIL PROTECTED]> Sent: Friday, September 27, 2002 10:08 AM Subject: RE: SQL question
> I don't know that there is knowing the way sorts usually work I'd say no. > This is my first posting to this group and I am just in the initial phases > of supporting MySQL as a backend for out application. > > If the numbers always precede the letters I would split them off into two > separate columns and index those columns. The numbers and letters may likely > represent something that could be used in your application as separate > entities. I may be off base but looks kind of like versioning. > > Sincerely, > > Will McCormick > > > > -----Original Message----- > From: John Almberg [mailto:[EMAIL PROTECTED]] > Sent: Thursday, September 26, 2002 8:05 PM > To: Mysql > Subject: SQL question > > > I'm trying to sort a table on a character-type field that contains mostly > numbers. This field always contains either a number or a number followed by > a character. Like '57' or '57a'. > > I'd like to sort the table *numerically* on this field, not *alphabetically* > on this field. That is, I'd like the table to be sorted like: > > 1 ... > 2 ... > 2a ... > 3 ... > 4d ... > > NOT like: > > 1 ... > 11 ... > 111a ... > 2a ... > 22 ... > > See what I mean? This is a common problem, I think, when you sort an > character type field that contains numbers. The sort comes out all wrong. > > Is there anyway I can achieve this sort using SQL? The target server is > running 3.22.32. Any ideas greatly appreciated! > > -- John > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
