PROBLEM WITH ORDER BY.

[samannoychatter]

I am facing a problem with sql query.

i want an output in the order
900
1200
1500
1800
2100
2400
2700
3000

whenever i am running a query ::

SELECT length FROM product_dimensions WHERE category_id=2 and sub_category_id=34 GROUP BY length ORDER by min(length) ASC;

i am getting the output

1200
1500
1800
2100
2400
2700
3000
900

if i run order by min(length) DESC

i am getting

900
3000
2700
2400
2100
1800
1500
1200

........

i cannot understand where i am going wrong. Please help me out.

even i have also tried DISTINCT::

SELECT DISTINCT length from product_dimensions WHERE category_id=2 and sub_category_id=34 ORDER BY length ASC;

Here also the output is like above.

1200
1500
1800
2100
2400
2700
3000
900

Please help me with the solutions.

[Pikachu2000]

The data type of the field is something other than a numeric data type. Either change the field type, (if the data that will be stored in that field will always be numeric), or cast the length field as unsigned integer in the ORDER BY clause.

SELECT DISTINCT length from product_dimensions WHERE category_id=2 and sub_category_id=34 
ORDER BY CAST( `length` AS UNSIGNED INT) ASC;

[samannoychatter]

Thanks bro.. it really helped me..my problem has been resolved. But can u explain what does this cast do?? and y are we using AS UNSIGNED INT , particularly UNSIGNED word.

[Pikachu2000]

It forces (casts) the data retrieved from the field as an unsigned integer, with unsigned meaning that all the numbers are cast as positive values. Doing so allows them to be ordered by value.

Trying to sort numbers stored in a non-numeric type data field will result in them being sorted as though they were alphabetic characters. That's why they end up sorted as they were.