CODEWITHSUNDEEP
phpmysql
Hai Truong IT
Hai Truong IT

Reputation: 4187

How to query order to asc (or desc) when column format is varchar

I have a table product(ID, name, quanlity) with quanlity is varchar(55)

With my data

1 | Iphone 3  | 7
2 | Iphone 3S | 8
3 | Iphone 4  | 79
4 | Iphone 4S | 9
5 | Iphone 5  | 10

And my query:

Select * From product order by quanlity DESC

But result can't order quanlity, because quanlity is varchar, How to query sort exactly

    4 | Iphone 4S | 9
    2 | Iphone 3S | 8
    3 | Iphone 4  | 79
    1 | Iphone 3  | 7    
    5 | Iphone 5  | 10

Upvotes: 0

Views: 82

Answers (3)

miah
miah

Reputation: 10433

You can CAST the column to a different type to allow for a natural numeric sort:

SELECT * FROM `product ` ORDER BY CAST(`quanlity` AS unsigned)

Upvotes: 0

bansi
bansi

Reputation: 57002

Not an elegant method, but you can cast the column to integer

SELECT * FROM product ORDER BY CAST(quanlity AS UNSIGNED)

Note: You are going to hit performance issue and unexpected order when you have something which is not integer. I suggest you better change the column type to integer (if it is possible -even with some trouble)

Upvotes: 3

L.C. Echo Chan
L.C. Echo Chan

Reputation: 596

Try this

SELECT * FROM `product ` ORDER BY convert(`quanlity`, decimal) DESC;

Upvotes: 1

Related Questions