MySQL – Change Row Order to Alphabetical then Numerical

MySQLorder-by

I have a table with a username as unique & primary key.

I ran this Query to order them in ascending order

ALTER TABLE  `table` ORDER BY  `Username` ;

So the rows are ordered like this

+-----------+----------+
| Username  | Password |
+-----------+----------+
| soho16793 | test1    |
| soho4595  | test2    |
| soho504   | test3    |
| soho931   | test4    |
+-----------+----------+

But I want them like this

+-----------+----------+
| Username  | Password |
+-----------+----------+
| soho504   | test3    |
| soho931   | test4    |
| soho4595  | test2    |
| soho16793 | test1    |
+-----------+----------+

How can I achieve this ?

Best Answer

I created a table called "billy".

mysql> SELECT * FROM billy ORDER BY f1;
+----------+-------+
| f1       | f2    |
+----------+-------+
| soho10   | test5 |
| soho12   | test1 |
| soho123  | test2 |
| soho1234 | test4 |
| soho222  | test3 |
+----------+-------+
5 rows in set (0.00 sec)

which doesn't give the correct sort order - 1234 is bigger than 222!

Then, from here, I got the SUBSTRING() function and formulated the following SQL:

mysql> SELECT CAST(SUBSTRING(f1, 5, LENGTH(f1)) AS UNSIGNED) 
AS fred, f2 FROM billy ORDER BY fred ASC;

which gives you the integers you require to sort on ("fred" is an alias for the new integer column):

+------+-------+
| fred | f2    |
+------+-------+
|   10 | test5 |
|   12 | test1 |
|  123 | test2 |
|  222 | test3 |
| 1234 | test4 |
+------+-------+
5 rows in set (0.00 sec)

mysql>

This also helped for CASTing. And thanks to @oNare for his question in comments below which led me to change this post from using the MID() function to the more standard SUBSTRING().

And the answer you require is given by 

SELECT * FROM billy ORDER BY CAST(SUBSTRING(f1, 5, LENGTH(f1)) AS UNSIGNED) ASC;

+----------+-------+
| f1       | f2    |
+----------+-------+
| soho10   | test5 |
| soho12   | test1 |
| soho123  | test2 |
| soho222  | test3 |
| soho1234 | test4 |
+----------+-------+
5 rows in set (0.00 sec)

mysql>
Related Question