The difficulty you're having is that what you call alphanumeric, MySQL sees as just a
string. How about a computed field with some usage of MySQL's substring_index function
to parse the string into pieces, if you know they will always have the given delimiter
(the -, in your example), and then sort based on just the numeric piece? For example,
with what you provided, this should work:
SUBSTRING_INDEX((SUBSTRING_INDEX(colname,'-',2)), '-', -1)
as the inner substring_index returns XY-1, XY-2, ... XY-n, while the outer
substring_index call returns just 1, 2, ... n. This has the advantage of not caring
how large your numeric values are, nor what characters or length of characters are
before or after the substring in question. Hope this helps.
>>> Chris Aitken <[EMAIL PROTECTED]> 10/29/01 09:31PM >>>
Hi All,
Ive been searching the archives and the documentation but dont really know
the terminology im looking for, or even if its do-able or not so I figure I
would just ask here instead......
I have a list of records in a table, and one of the fields is alpha
numeric. They look like this.......
XY-1-BLAH
XY-2-BLAH
XY-3-BLAH
XY-4-BLAH
XY-5-BLAH
XY-6-BLAH
XY-7-BLAH
XY-8-BLAH
XY-9-BLAH
XY-10-BLAH
XY-11-BLAH
XY-12-BLAH
XY-13-BLAH
Now whats happening, im sorting my query by this field and I want it to
sort as it is above..... however its putting it as the following order....
XY-1-BLAH
XY-10-BLAH
XY-11-BLAH
XY-12-BLAH
XY-13-BLAH
XY-2-BLAH
XY-3-BLAH
Is there a way to sort this field (which is set to VARCHAR) by the first
list, not the second list ?
I know I can load all the entries up in an array and use a PHP type array
sort function, but thats not really the outcome I need because ultimately I
want to do a descending sort, and just grab the last record......
Am I making sense, cause ive managed to confuse myself a little here :)
Cheers
Chris
--
Chris Aitken - Administration/Database Designer - IDEAL Internet
email: [EMAIL PROTECTED] phone: +61 2 4628 8888 fax: +61 2 4628 8890
__-----------------------------------------__
# ln -s /dev/null /bin/laden
If you don't like my typing, all flames to /bin/laden
---------------------------------------------------------------------
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
