Hassan Schroeder wrote:
Scott Gifford wrote:
select * from price where amount = 3.45 // doesn't work
Floating point numbers are very rarely equal, because of small
rounding errors that happen all the time. Probably the actual number
in the database is something like '3.44000000000000001'.
Try something like this:
select * from price where amount > 3.44 and amount <= 3.46
I hope the "<=" was a typo? Depending on your data and preferred method of
rounding,
SELECT * FROM price WHERE amount >= 3.445 AND amount < 3.455;
may be better. In fact, the difference between 3.45 and the actual value stored
is likely quite small.
mysql> select 3.45 + 0.0000000000000000;
+---------------------------+
| 3.45 + 0.0000000000000000 |
+---------------------------+
| 3.4500000000000002 |
+---------------------------+
1 row in set (0.01 sec)
Problems with FLOAT and equality are documented
<http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html>.
In general, you could pick a tolerance
SET @tol = .001;
to set your range
SELECT * FROM price
WHERE amount > 3.45 - @tol AND amount < 3.45 + @tol;
If your amounts are all supposed to be strictly 2 decimal places, .01 would work
for your tolerance. In that case, though, you should probably be using DECIMAL
instead of FLOAT
<http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html>.
Or maybe ... where ROUND(amount,2) = 3.45;
Unlike the range queries above, this one cannot use an index on the amount
column to select rows, so it is a guaranteed full table scan.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
