CODEWITHSUNDEEP
mysqlsqldatabasefloating-point
Edy Elwood
Edy Elwood

Reputation: 137

SQL MIN() strange value

I'm working on a small webshop and I'm trying to get the lowest price for a product (photos).

So I check to see what the lowest added price is (photos can be ordered in different sizes):

SELECT 
  MIN(price) as price 
FROM 
  rm_prices 
WHERE 
  photo_id = '47' AND 
  price != '0'

This returns the lowest value found for this product.

When I check my db, I see that the lowest value is 1256.3.
When I print my result, the number give is 1256.30004882813.
The value is set as a FLOAT.

Why is the result 1256.30004882813 and not 1256.3?

Upvotes: 8

Views: 1129

Answers (3)

Daniel Vassallo
Daniel Vassallo

Reputation: 344301

Because the real number 1256.3 cannot be represented precisely in floating point.

You should be using a fixed-point datatype for monetary data. Quoting from the MySQL documentation:

The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data.

Upvotes: 13

Brian Hooper
Brian Hooper

Reputation: 22044

1256.3 does not have an exact representation in floating-point arithmetic; for that reason it is usually better to store sums of money as INTEGER or DECIMAL data types.

More information may be found here.

Upvotes: 1

zed_0xff
zed_0xff

Reputation: 33217

It's a side effect of FLOAT column type, you can try to store your prices in a DECIMAL data type, see the mysql manual for more info about it: http://dev.mysql.com/doc/refman/5.1/en/fixed-point-types.html

Upvotes: 6

Related Questions