I have a field number
of type varchar
. Even though it is of type varchar
, it stores integer values with optional leading zeros. A sort orders them lexicographically ("42"
comes before "9"
). How can I order by numeric values ("9"
to come before "42"
)?
Currently I use the query:
SELECT * FROM table ORDER BY number ASC
The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
Unfortunately, MySQL does not provide any built-in natural sorting syntax or function. The ORDER BY clause sorts strings in a linear fashion i.e., one character a time, starting from the first character.
Try this
SELECT * FROM table_name ORDER BY CAST(field_name as SIGNED INTEGER) ASC
There are a few ways to do this:
00100
intact with the leading zeros.insert
/update
trigger to ensure it's set correctly whenever the string column changes.Since the vast majority of databases are read far more often than written, this third option above amortises the cost of the calculation (done at insert
/update
) over all selects. Your selects will be blindingly fast since they use the numeric column to order (and no per-row functions).
Your inserts and updates will be slower but that's the price you pay and, to be honest, it's well worth paying.
The use of the trigger maintains the ACID properties of the table since the two columns are kept in step. And it's a well-known idiom that you can usually trade off space for time in most performance optimisations.
We've used this "trick" in many situations, such as storing lower-cased versions of surnames alongside the originals (instead of using something like tolower
), lengths of identifying strings to find all users with 7-character ones (instead of using len
) and so on.
Keep in mind that it's okay to revert from third normal form for performance provided you understand (and mitigate) the consequences.
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