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
Alphanumeric ordering is done using the current language sort order on the client machine as defined by the operating system (i.e. Windows). The user requests the sort by clicking on the column header. A grid must have column headings in order to be sorted by the user.
you should split the strings in two first; the other part being the integer part, and the other the string part. then first compare the integers - if the integers are not equal, the string that should appear first is the one with the smaller integer part.
People use different tricks to do this. I Googled and find out some results each follow different tricks. Have a look at them:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With