[snip] I'm having trouble with ordering. I've got data in a varchar field that currently gets ordered like this when I use 'order by myfield asc':
aristo 1001 aristo 156 aristo 222 I'd like it to order like this: aristo 156 aristo 222 aristo 1001 How can I do this in MySQL? Is there a way to take the numbers into account when using order by? [/snip] SELECT columnName, RIGHT(columnName, 4) AS theNumber FROM tableName ORDER BY theNumber +-------------+-----------+ | columnName | theNumber | +-------------+-----------+ | aristo 156 | 156 | | aristo 222 | 222 | | aristo 1001 | 1001 | | aristo 2317 | 2317 | +-------------+-----------+ Now, this will only work if you specify the correct number in the RIGHT() function. Since you had 4 digit numbers, I used 4. But if this number is longer, you need to increase your integer in the RIGHT() function as it will not be able to determine the length of the number. mysql> select info AS columnName, SUBSTRING_INDEX(info, " ", -1) AS theNumber from tblStuff ORDER BY theNumber; +-------------+-----------+ | columnName | theNumber | +-------------+-----------+ | aristo 1001 | 1001 | | aristo 156 | 156 | | aristo 222 | 222 | | aristo 2317 | 2317 | +-------------+-----------+ As you can see, the SUBSTRING_INDEX() function retrieves the number after the space, but the query does not sort this the way that you want. RIGHT() takes into account the space before the 3 digit integer and sorts numbers with spaces first, which works for you in this case. The problem with these solutions is that they may not work in every case needed. HTH! Jay --------------------------------------------------------------------- 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
