MySQL - sort text column

Question

I have table:

id (int) | floor (text)
-----------------------
1        | 1
2        | 10
3        | 7
4        | Ground floor

I want the result from my query to sort the data on the text column floor. Is possible to get result like that?

id (int) | floor (text)
-----------------------
4        | Ground floor
1        | 1
3        | 7
2        | 10

Answer 1

Here is a way you can do it using mysql's silent conversion technique and then applying it to order by clause

select * from mytable
order by 
case 
 when `floor`+0 = 0 then 0 
   else 1
 end , `floor`+0,`floor`

http://sqlfiddle.com/#!9/119d2/2

Or even more easy

select * from mytable
order by `floor`+0,`floor`