Correct method for "order by."

MySQL order method

[russkern]

I have a small issue that I'm sure has an easy fix, but I'm kind of fried and am plain missing it.

I'm reporting a list of products out of a MySQL DB and ordering them by "unitprice" which is numeric.

The list it returns mostly correct except that the order goes as such:

1.34
11.45
11.56
2.34
3.56
9.45

What is the correct "order By" method (or what am I doing wrong with the current one) to get it to order correctly.

1.34
2.34
3.56
9.45
11.45
11.56

Thanks in advance.

Russ

[WesleyC]

The numbers are being sorted as text. In order to solve this, you need to either make sure the database field is indeed numeric (such as an integer or decimal datatype) or cast the values you're sorting before you sort them. For example:

SELECT * FROM myTable ORDER BY CAST(unitprice AS DECIMAL(6,2)) ASC

[russkern]

Thanks for the answer... I see the problem, however when I change the field type to either int or decimal,and then I try to update the unit price via my form, It truncates everything after the decimal and only posts a whole number.

IS there something else I need to do here?

R

UPDATE: Nevermind.. I found it... I used your other example above and figured out what I was doing wrong... As a decimal, I needed to add the length (6,2)

One question... is the first number(6) the number of places BEFORE the decimal? Or the TOTAL length of the string?

[WesleyC]

That should be the number of places before the decimal. The second number is the number of places after it. Different SQL servers may handle it differently, however--I'd recommend a check with MySQL's documentation to make sure.

[russkern]

Will do...

Thank you.

[stajer]

I recommend you don't try to use your db to do formatting. Let your front end handle formatting.