Mysql order not working properly

Question

I use a simple order by clause to display products according to available quantity

The following is the query I'm using:

 SELECT * FROM productsinfo ORDER BY quantity desc 

A request that gives an error, but the sort order is not correct. Anyone please tell me where I am going wrong.

EDIT

Checked that my quentity clumn is of type varchar . I save the values ​​in the format 1,215 10,456 .

Answer 1

Maybe your varchar quantity column so it doesn't sort as numbers. Please check. You need to specify it in integer type

Try the following:

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

OR Use the trick below.

 SELECT * FROM productsinfo ORDER BY quantity+0 DESC 

Answer 2

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

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

Answer 3

You need to first remove , from the value and turn the result into a number. Try:

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

Answer 4

The answer to the question: - for the varchar data type, it compares data on a whole series from left to right, which means that it processes 100 units less than 11. Therefore, therefore, comparing and sorting by varchar data type for integer data is a bad choice. Convert it to int using the fill in the query or modify the table.