CAST()ing a sort in MySQL

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 LL.

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.

2 thoughts on “CAST()ing a sort in MySQL

  1. CREATE TABLE mytest(
    emp_id VARCHAR(11) NOT NULL,
    emp_name VARCHAR(100) NOT NULL

    (‘EMP_1′,’Name 1′),
    (‘EMP_3′,’Name 3′),
    (‘EMP_5′,’Name 5′),
    (‘EMP_2′,’Name 2′),
    (‘EMP_7′,’Name 7′),
    (‘EMP_9′,’Name 9′),
    (‘EMP_11′,’Name 11′),
    (‘EMP_21′,’Name 21′),
    (‘EMP_4′,’Name 4′);

    SELECT * FROM mytest

Leave a Reply