CODEWITHSUNDEEP
mysql
Paul-B-PC
Paul-B-PC

Reputation: 57

MySQL - sort text column

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

Upvotes: 1

Views: 724

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

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`

Upvotes: 2

Related Questions