Storing decimal values in MySQL

[TWTCommish]

Simple enough (I hope): how can I order by a field (in MySQL) containing decmical values. Right now. 9.88 shows up ahead of 14.90. The field is TEXT, because I've found that the INT field type strips all data after the decimal when it's entered into the database. Is there another field type I can use, perhaps?

[freakysid]

An integer is by definition a whole number. Real numbers you can chose from the following types:

from : http://www.mysql.com/doc/C/o/Column_types.html

FLOAT(precision) [ZEROFILL]
A floating-point number. Cannot be unsigned. precision can be <=24 for a single-precision floating-point number and between 25 and 53 for a double-precision floating-point number. These types are like the FLOAT and DOUBLE types described immediately below. FLOAT(X) has the same range as the corresponding FLOAT and DOUBLE types, but the display size and number of decimals is undefined. In MySQL Version 3.23, this is a true floating-point value. In earlier MySQL versions, FLOAT(precision) always has 2 decimals. Note that using FLOAT may give you some unexpected problems as all calculation in MySQL is done with double precision. See section 21.19 Solving Problems with No Matching Rows. This syntax is provided for ODBC compatibility.
FLOAT[(M,D)] [ZEROFILL]
A small (single-precision) floating-point number. Cannot be unsigned. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. The M is the display width and D is the number of decimals. FLOAT without an argument or with an argument of <= 24 stands for a single-precision floating-point number.
DOUBLE[(M,D)] [ZEROFILL]
A normal-size (double-precision) floating-point number. Cannot be unsigned. Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. The M is the display width and D is the number of decimals. DOUBLE without an argument or FLOAT(X) where 25 <= X <= 53 stands for a double-precision floating-point number.
DOUBLE PRECISION[(M,D)] [ZEROFILL]

REAL[(M,D)] [ZEROFILL]
These are synonyms for DOUBLE.
DECIMAL[(M[,D])] [ZEROFILL]
An unpacked floating-point number. Cannot be unsigned. Behaves like a CHAR column: ``unpacked'' means the number is stored as a string, using one character for each digit of the value. The decimal point and, for negative numbers, the `-' sign, are not counted in M (but space for these are reserved). If D is 0, values will have no decimal point or fractional part. The maximum range of DECIMAL values is the same as for DOUBLE, but the actual range for a given DECIMAL column may be constrained by the choice of M and D. If D is left out it's set to 0. If M is left out it's set to 10. Note that in MySQL Version 3.22 the M argument had to includes the space needed for the sign and the decimal point.
NUMERIC(M,D) [ZEROFILL]
This is a synonym for DECIMAL.

for the storage requirement of each of these types consult: http://www.mysql.com/doc/n/o/node_178.html

[TWTCommish]

Bingo: changed the column to FLOAT. You the man. Thanks Sid.

[freakysid]

You should prefer DOUBLE. As the manual says FLOAT is only provided for ODBC compatability.

[TWTCommish]

Oh. Is FLOAT inferior in some way then?

[freakysid]

From my earier post:

Note that using FLOAT may give you some unexpected problems as all calculation in MySQL is done with double precision. See section 21.19 Solving Problems with No Matching Rows. This syntax is provided for ODBC compatibility.

You should understand that there is some very small degree of error involved in converting a non-integer real number from a base10 decimal number to a base2 binary number as is done in the case of FLOAT and DOUBLE. Most of the time this is not going to be an issue and the results of any calculation will be highly accurate. However, you can get unexpected results when you are trying to compare two floating point values which in the decimal system are equal but which end up being approximated differently as binary numbers.

Anyway, these are not important issues most of the time. But if the manual tells me to prefer DOUBLE over FLOAT then that's good enough for me