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), e.g.:

A1
534G
G46A
100B
100A
100JE

etc....

Thanks!

update: people asking for query

select * from table order by name asc

Solution 1:

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

Edit:

I have just added the code of each link for future visitors.

Alpha Numeric Sorting 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

Natural Sorting in MySQL

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  

Sorting of numeric values mixed with alphanumeric values

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

Solution 2:

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

Solution 3:

I know this post is closed but I think my way could help some people. So there it is :

My dataset is very similar but is a bit more complex. It has numbers, alphanumeric data :

1
2
Chair 
3
0
4
5
-
Table
10
13
19
Windows
99
102
Dog

I would like to have the '-' symbol at first, then the numbers, then the text.

So I go like this :

SELECT name, (name = '-') boolDash, (name = '0') boolZero, (name+0 > 0) boolNum 
FROM table 
ORDER BY boolDash DESC, boolZero DESC, boolNum DESC, (name+0), name

The result should be something :

-
0    
1
2
3
4
5
10
13
99
102
Chair
Dog
Table
Windows

The whole idea is doing some simple check into the SELECT and sorting with the result.

Solution 4:

I hate this, but this will work

order by lpad(name, 10, 0)  <-- assuming maximum string length is 10
                            <-- you can adjust to a bigger length if you want to