CODEWITHSUNDEEP
mysqldatabase
user1264520
user1264520

Reputation:

Mysql order by not working properly

I am using simple order by clause to show products according to available quantity

Below is the query I am using:

SELECT * FROM productsinfo ORDER BY quantity desc

Query giving no error but sorting order is not correct.Anyone please tell me where I am wrong.

EDIT

Have checked my quentity clumn is varchar type.I am storing values in 1,215 10,456 format.

Upvotes: 12

Views: 27642

Answers (4)

user319198
user319198

Reputation:

Might be your quantity column is varchar type so it's not sorting as numbers.Please check. You need to cast it in integer type

Try below:

SELECT * FROM productsinfo ORDER BY CAST(quantity AS UNSIGNED ) desc

OR Use below trick. 

SELECT * FROM productsinfo ORDER BY quantity+0 DESC

Upvotes: 26

user1285324
user1285324

Reputation:

The exect answer is:- for varchar data type it compares integert data from left to right that means it treat 100 less the the 11. So thats why comparing and sorting on varchar data type for integer data is a bad choice. Convert it to int using cast in a query or alter your table.

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

You'll have to first remove the , from the value and turn the result into a number. Give this a try:

SELECT * FROM productsinfo
ORDER BY REPLACE(quantity, ',', '')+0 DESC

Upvotes: 1

John Woo
John Woo

Reputation: 263693

I think you define quantity as VarChar. Because if it's a Number (int, smallint, decimal,..) the order will be definitely correct.

SELECT *, CAST(quantity AS int) QuantityA 
FROM productsinfo   
ORDER BY QuantityA desc

Upvotes: 3

Related Questions