MySQL ‘Order By’ – sorting alphanumeric correctly

by

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

More Related Contents:

  1. MySQL order by before group by
  2. Why does MYSQL higher LIMIT offset slow the query down?
  3. MySQL Orderby a number, Nulls last
  4. How does MySQL process ORDER BY and LIMIT in a query?
  5. Ordering by the order of values in a SQL IN() clause
  6. Order a MySQL table by two columns
  7. Ordering by specific field value first
  8. MySQL “Group By” and “Order By”
  9. Using union and order by clause in mysql
  10. SQL order string as number
  11. GROUP_CONCAT ORDER BY
  12. What is MySQL row order for “SELECT * FROM table_name;”?
  13. Slow query when using ORDER BY
  14. Select the 3 most recent records where the values of one column are distinct
  15. MySQL – Control which row is returned by a group by
  16. MySQL: Sort GROUP_CONCAT values
  17. MySQL order by “best match”
  18. Order by COUNT per value
  19. MySQL/MariaDB – order by inside subquery
  20. MySQL Order before Group by
  21. SQL Order By Count
  22. ORDER BY date and time BEFORE GROUP BY name in mysql
  23. Returning query results in predefined order
  24. Mysql delete statement with limit
  25. MySQL – ORDER BY values within IN()
  26. Mysql: Order by like?
  27. Sort the rows according to the order specified in WHERE IN clause
  28. ORDER BY alphabet first then follow by number
  29. MYSQL Select One Random record from each Category
  30. mysql order by, null first, and DESC after

Leave a Comment