I was asked to sort a table of lease data by floor in descending order today—simple, right? But after updating the query with
ORDER BY floor DESC, I noticed the results were wrong. The 9th floor was always at the top and the 10th floor and above were between the second and first floors.
It was immediately obvious—the floor field was not stored as numeric data, but as character data. This struck me as odd, so I investigated the DB structure and values. The floor field was definitely being stored as character data, but why? The reason: the client wanted to store certain floors as
So given this structure, how could I quickly and easily sort the floors? The answer: MySQL’s
CAST function. By casting the floor field as an integer, the numeric floors would sort correctly. Even better, the character data
LL would cast to 0, preserving a correct sort.
I updated the query with
ORDER BY CAST(floor as UNSIGNED) DESC and obtained the desired results. Learn more about MySQL’s cast functions and operators.