I was working on a project recently where the e-commerce client organized their inventory using product SKU values formatted numerically. The MySQL field type was a varchar yet the values entered where formatted like: 10123, 65432, 98765, etc.
The client’s most recent product update presented a sort order problem. Their new set of products had SKU values that surpassed 5-digits, formatted like 100123. The default sort order is to show the highest numerical SKUs at the top. Because MySQL expects the SKU to be a string value, the actual numerical values sorted incorrectly — all the newest products were at the bottom.
Luckily there is a quick and easy fix for this type of problem. You can force a MySQL SELECT statement to sort naturally. A natural sort is really just a term for how humans might commonly order a set of information. It is easy to achieve in MySQL:
SELECT `product_title`, `product_sku` FROM `products` ORDER BY `product_sku` + 0 DESC
By adding a “+ 0″ to the order by column, this forces the data to be sorted naturally without altering the results. There you have it, a quick and easy fix for sorting string values in a usable way.


