CODEWITHSUNDEEP
phpmysqlsql
mike621
mike621

Reputation: 93

Is less than not working in a SQL query PHP

I have a MYSQL table like this:

point     | z_id | ok|
300697.12 | 391  | 1 |
300701.88 | 391  | 1 |
300576.78 | 391  | 1 |
300576.78 | 391  | 1 |

and I run this query

SELECT MAX( `point` ) as `max`
FROM `my_table`
WHERE `point` < 300701.88
AND `z_id`='391' AND `ok`=1

It should return 300697.12, but I get 300701.88, I tried to put the number into '', but I get the same results. Even if I run the query like this

SELECT *
FROM `my_table`
WHERE `point` < 300701.88
AND `z_id`='391' AND `ok`=1

the 300701.88 result is still there.

My field type for point is float(10,2).

Is there a problem with sql considering the number as a text? Thanks a lot :)

Upvotes: 1

Views: 214

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

Your are confusing float() and decimal() data types. A MotoGP explains, the right type is decimal(). However, you should also be able to express the logic using cast():

where point < cast('300701.88' as float(10, 2))

This might be useful if you are stuck with the particular definitions in the table. The idea is to ensure that the values are compared as exactly the same type.

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93724

From Mysql Docs

Floating-point numbers sometimes cause confusion because they are approximate and not stored as exact values. A floating-point value as written in an SQL statement may not be the same as the value represented internally. Attempts to treat floating-point values as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies. The FLOAT and DOUBLE data types are subject to these issues. For DECIMAL columns, MySQL performs operations with a precision of 65 decimal digits, which should solve most common inaccuracy problems

Clearly float datatype is the problem here which is also known as approximate datatype which will not store the exact value. This is the reason float should be avoided.

Changing the datatype from float(10,2) to Decimal(10,2) will fix your problem

Here is the demo

Upvotes: 3

Related Questions