mysql Max behaviour with numeric vals in varchar field

Question

We have a database table that has keys and values where the value can be anything in a varchar field. The values are for attributes about an item and one attribute is price.

To make a select field for prices, I was using the Max() function to find the greatest value in the value column which seemed to work.

Then when we got prices over 100,ドル they started to not get returned as the max value. I under stand that this is because it is a string value and not numeric.

The confusion comes when running a command like select max(value) from attributes where value > 100 because now the statement recognises that 101 is> 100 but 99 is not so returns 101 as the max value, however without the where value > 100 clause, 99 is treated as> 101. Why does the> 100 clause work as a numeric comparison but max does not?

Is there a reason that this happens?

• ajreal: possible duplicate of varchar to number conversion for sorting in MySQL. Do not mixed varchar with numeric, you can do the same max( cast(value as unsigned) ) but is no ideal
• Luke: great thanks mate, if you put that in an answer I can accept it. Or should I just close the question as its pretty much a duplicate. Do you know why the > clause works? that was the real reason for the question.
• Karolis: @Luke without type conversion > clause doesn't work too. Try: select '1000' > '99'
• Luke: This statment: select max(value) from attributes where value > 100 did work though, thats what confused us, but select max(value) from attributes where value > 50 did not, it still selected 99 as the greatest value.

Answer 1

Do not mixed varchar with numeric,
idea solution is only stored numeric for used of max aggregate function,
alternatively

max( cast(value as unsigned) )

When you are doing a MAX, is a cast to string.
When you are doing comparison, is a cast to numeric.

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

• ajreal: Unless a quote has been applied?
• Karolis: Oh, I had in my mind the string comparison in general. In this case, one of operands in > clause is a muneric value, so the second operand is converted to numeric value automatically. Sorry, you are right. I was inattentive.