Mysql Max behavior with numeric vals in varchar field

Question

We have a database table in which there are keys and values, where the value can be anything in the varchar field. The values ​​for the attributes are about the element, and one attribute is the price.

To make a selection box for prices, I used the Max() function to find the largest value in the column of values ​​that seemed to work.

Then, when we received prices above £ 100, they did not receive as the maximum value. I understand that this is because it is a string value, not a numeric one.

The confusion arises when you run a command of the type select max(value) from attributes where value > 100 , because now the operator recognizes that 101> 100, but 99 does not return 101 as the maximum value, but without a clause where value > 100 99 is treated as> 101 Why does a sentence> 100 work like a numerical comparison, but max doesn't work?

Is there a reason this is happening?

Answer 1

Do not mix varchar with numeric,
the solution to the idea is stored only a numerical value for the max aggregate function,
as an alternative

 max( cast(value as unsigned) ) 

When you do max , this is a row in a row.
When you perform a comparison, it is different from the number.

Cause?
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_max
(it returns the maximum string value)