MySQL 'Order By' - sorting alphanumeric correctly

I want to sort the following data items in the order they are 

presented below (numbers 1-12):

1
2
3
4
5
6
7
8
9
10
11
12

However, my query - using order by xxxxx asc sorts by the first digit 

above all else:

1
10
11
12
2
3
4
5
6
7
8
9

Any tricks to make it sort more properly?

Further, in the interest of full disclosure, this could be a mix of letters and 

numbers (although right now it is not), I.E.:

A1
534G
G46A
100B
100A
100JE

update: people asking for query

select * from table order by name asc

 Answers

People use different tricks to do this. I Googled and find out some results 

each follow different tricks. Have a look at them:

• Alpha Numeric Sorting in MySQL
• Natural Sorting in MySQL
• Sorting of numeric values mixed with alphanumeric values
• mySQL natural sort
• Natural Sort in MySQL

Given input

1A 1a 10A 9B 21C 1C 1D

Expected output

1A 1C 1D 1a 9B 10A 21C

Query

Bin Way
===================================
SELECT 
tbl_column, 
BIN(tbl_column) AS binray_not_needed_column
FROM db_table
ORDER BY binray_not_needed_column ASC , tbl_column ASC

-----------------------

Cast Way
===================================
SELECT 
tbl_column, 
CAST(tbl_column as SIGNED) AS casted_column
FROM db_table
ORDER BY casted_column ASC , tbl_column ASC

Given input

Table: sorting_test
 -------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
 -------------------------- -------------
| test1                    | 1           |
| test12                   | 2           |
| test13                   | 3           |
| test2                    | 4           |
| test3                    | 5           |
 -------------------------- -------------

Expected Output

 -------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
 -------------------------- -------------
| test1                    | 1           |
| test2                    | 4           |
| test3                    | 5           |
| test12                   | 2           |
| test13                   | 3           |
 -------------------------- -------------

Query

SELECT alphanumeric, integer
       FROM sorting_test
       ORDER BY LENGTH(alphanumeric), alphanumeric  

Given input

2a, 12, 5b, 5a, 10, 11, 1, 4b

Expected Output

1, 2a, 4b, 5a, 5b, 10, 11, 12

Query

SELECT version
FROM version_sorting
ORDER BY CAST(version AS UNSIGNED), version;

Hope this helps

---

Just do this:

SELECT * FROM table ORDER BY column `name`+0 ASC

Appending the +0 will mean that:

0, 10, 11, 2, 3, 4

becomes :

0, 2, 3, 4, 10, 11

---

I had some good results with

SELECT alphanumeric, integer FROM sorting_test 

ORDER BY CAST(alphanumeric AS UNSIGNED), alphanumeric ASC

---

This should sort alphanumeric field like: 1/ Number only, 

order by 1,2,3,4,5,6,7,8,9,10,11 etc... 2/ 

Then field with text like: 1foo, 2bar, aaa11aa, aaa22aa, b5452 etc...

SELECT  MyField
FROM MyTable
order by 
    IF( MyField REGEXP '^-?[0-9]+$' = 0, 
    9999999999 ,  
    CAST(MyField AS DECIMAL) 
    ), MyField

The query check if the data is a number, if not put it to

 9999999999 , then order first on this column, then order on data with text

Instead of trying to write some function and slow down the 

SELECT query, I thought of another way of doing this...

Create an extra field in your database that holds the result from the

 following Class and when you insert a new row, run the field value

 that will be naturally sorted through this class and save its result in

 the extra field. Then instead of sorting by your original field, 

sort by the extra field.

This works for type of data: Data1, Data2, Data3 ......,Data21. Means "Data" String is common in all rows.

For ORDER BY ASC it will sort perfectly, For ORDER BY DESC not suitable.

SELECT * FROM table_name ORDER BY LENGTH(column_name), column_name ASC;